Manually building PostgreSQL for 1C 8.2, or easy ways are not for us!

    This article describes how to manually build a PostgreSQL database for 1C under Gentoo Linux.
    I will not talk about all the dead-end branches of our labyrinth for building postgres for 1C. I will only describe what is nowhere on the Internet — what we smoked using the scientific poke method. Despite the abundance of manuals for installing postgres for 1C, none of them solved our main rake, they all turned out to be superficial and did not explain anything. Therefore, we considered it our duty to fill this gap :-)


    To begin with, we use Gentoo, and we don’t install any RPM packages. It was not possible to install the 1C package in rpm format for Gentoo (it’s long to explain why), so downloading src.rpm and automatically building it using rpmbuild was no longer possible. All that remained for us was to transfer rpm2tgz from rpm format to a simple tar.gz archive and unpack it, which we did. The following is a step-by-step guide.

    0. Preparing the environment: mkdir / tmp / t5 && cd / tmp / t5 (there were 5 starts from the very beginning :-)

    1. Download the package with all the required files from the official site: wget v8.1c.ru/overview/postgresql_patches/8 -4-1 / postgresql-8.4.1-1.1C.src.rpm

    2. Run : rpm2tar postgresql-8.4.1-1.1C.src.rpm && rm postgresql-8.4.1-1.1C.src.rpm

    3. Unpack the archive: tar xf postgresql-8.4.1-1.1C.src.tar. All files are unpacked into the current directory, so it’s better to do this in a clean folder. Also, immediately unpack postgresql-8.4.1.tar.bz2 and go to this folder:
    tar xfj postgresql-8.4.1.tar.bz2 && cd cd postgresql-8.4.1

    4.Open the file ../postgresql-8.4.1C. spec and we have been studying it for a very long time :-) It describes everything that rpmbuild would do (if everything was so simple!). We find the section where patches are applied, and in the same order we begin to do this (from the folder with postgres):

    patch -p1 <../rpm-pgsql.patch
    patch -p1 <../postgresql-logging.patch
    patch -p1 < ../postgresql-perl-rpath.patch
    patch -p1 <../postgresql-prefer-ncurses.patch
    patch -p0 <../1c_FULL_84-0.19.2.patch (p0, not p1!)
    patch -p1 <../postgresql-1c-8.3.patch - send jokes, the patch does not overlap! And this is in the official delivery. We never found what changes the rpm spec so that such a patch overlaps. Fortunately, this patch is small and understandable, it just changes the default config options, so we will come back after installation and fix the config with the handles. In the meantime, we scored on it and move on.
    patch -p0 <../applock-1c-8.4.0.patch

    5. Next, we study the build rpm spec section, and look at what parameters configure is called with. It is hard to believe that 1C will use perl, python or tcl (and we raise the database exclusively for 1C, we don’t want to keep our other databases in the 1C developers database, let them live in the normal original post-game), so we simply ignore these options. Total we used:
    ./configure --disable-rpath --enable-nls --disable-integer-datetimes --enable-thread-safety

    6. make && make install. The postgres root is copied to / usr / local / pgsql.

    Next, you need to install additional libraries from the contrib folder. Remember, one patch did not overlap? It adds to contrib / Makefile 3 additional libs from 1C: mchar, fulleq and fasttrun. To build mchar, you will need the optional ICU component 3.8. Therefore, we will make a small digression and put it:

    mkdir / tmp / icu && cd / tmp / icu && wget download.icu-project.org/files/icu4c/3.8.1/icu4c-3_8_1-src.tgz
    tar zxf icu4c-3_8_1- src.tgz && cd icu / source
    ./configure --prefix = / usr / local / icu && make && make install

    ICU is set to / usr / local / icu. But we only need a library connected at runtime, therefore, after installing the postgres, we will kill this folder.

    Let's get back to the contrib build.


    7. You need to edit the contrib / mchar / Makefile file and fix PG_CPPFLAGS to the ICU path:
    PG_CPPFLAGS = -I / usr / local / icu / include.
    Also, correct the path to SHLIB_LINK at the very bottom in the same file:
    SHLIB_LINK + = -L / usr / local / icu / lib -licuuc -l $ (ICUNAME) -Wl, -rpath, '$$ ORIGIN'

    8. Next, add the handle three 1C components in WANTED_DIRS in contrib / Makefile (line 38, see v8.1c.ru/overview/postgresql_patches/8-4-1/postgresql-1c-8.4.patch ):
    vacuumlo \
    mchar \
    fulleq \
    fasttrun

    9. Let's build contrib:
    make -C contrib && make -C contrib install

    10. Use the pens to copy the required ICU libraries to the folder with the following folder:
    cp -a / usr / local / icu / lib / * / usr / local / pgsql / lib /
    rm -rf / usr / local / icu

    11. The postgres is built and installed in / usr / local / pgsql. Configs will not be edited yet. Create a system account under which the base will work:

    useradd -d / usr / local / pgsql / -s / bin / bash pgmaster
    mkdir / usr / local / pgsql / data
    chown pgmaster / usr / local / pgsql / data && chmod 700 / usr / local / pgsql / data
    cat >> /usr/local/pgsql/.bash_profile < export LANG = ru_RU.UTF-8
    export PGPORT = 5434
    export PATH = / usr / local / pgsql / bin: / usr / local / pgsql / sbin: $ PATH
    export LD_LIBRARY_PATH = / usr / local / pgsql / lib
    EOF

    We have on the server already runs one postgres, so for 1C we chose a different port and system account, created a bash profile that runs with su - pgmaster, so all paths and variables are set.

    12. Initialize and run the database:

    mkdir / usr / local / pgsql / log && chown pgmaster / usr / local / pgsql / log
    su - pgmaster
    initdb -D / usr / local / pgsql / data
    postgres -h 0.0.0.0 -p 5434 -D / usr / local / pgsql / data

    The server started quietly without losing the control terminal.
    Change the password to the super-browser (screen drives, hehe):

    su - pgmaster
    psql template1
    psql (8.4.1)
    Type "help" for help.
    template1 = # alter user pgmaster with password 'pass-pass';
    ALTER ROLE
    template1 = #
    CTRL + D

    Now, put out the postgres in the first console using CTRL + C, and it's time to edit the configs.

    13. The data / pg_hba.conf file, in it you need to change trust to md5, and also add an entry that allows access to the network:

    local all all md5
    host all all 127.0.0.1/32 md5
    host all all 0.0.0.0/0 md5

    14 The file data / postgresql.conf, it is necessary to set the following options in it (part of the non-overlay patch, part for debugging):

    log_destination = 'stderr'
    logging_collector = off # turn off while logging in data / pg_log, will output to the console)
    log_statement = 'all' # we want to see that 1C will be returned to the database!
    listen_addresses = '*' # on which interfaces to listen
    effective_cache_size = 512MB # 1C recommendation
    max_locks_per_transaction = 150 # 1C recommendation

    15. Shared memory. Either sysctl -w kernel.shmmax = 256000000, or edit /etc/sysctl.conf, add kernel.shmmax = 256000000 there, and then sysctl -p.

    16. And now the fun begins. We start postgres again (postgres -D data -p 5434). We connect and create a base for 1C:

    $ psql template1
    Password:
    psql (8.4.1)
    Type "help" for help.
    template1 = #

    17. Next, you need to create the plpgsql language in the template1 database:

    template1 = # create language plpgsql;
    CREATE LANGUAGE
    template1 = #

    18. Next, you need to fill in it with SQL code from 3 components from 1C. Nowhere on the Internet about this operation is not said. We studied the entire RPM spec, performed make check, test, etc. But they didn’t find how all this code gets into template1 in RPM packages. Therefore, we do this with our hands:

    template1 = # \ i /tmp/t5/postgresql-8.4.1/contrib/mchar/mchar.sql
    template1 = # \ i /tmp/t5/postgresql-8.4.1/contrib/fulleq/fulleq .sql
    template1 = # \ i /tmp/t5/postgresql-8.4.1/contrib/fasttrun/fasttrun.sql

    If you suddenly swear at mchar.so for loading, which in turn did not find libicu *, then you forgot about LD_LIBRARY_PATH or copy ICUs to / usr / local / pgsql / lib, read first :-P

    19. But that's not all !! ! The main traffic jam, because of which they could not move on. We set it on base 1C, look in the logs:

    LOG: statement: select 1 from pg_proc where proname in ('plpgsql_call_handler', 'datediff', 'mchar_in', 'mc_mv_icase_cmp', 'fasttruncate')

    And after that 1C says: “Bad at you database is not suitable. "
    We enter this request from the console:

    template1 = # select 1 from pg_proc where proname in ('plpgsql_call_handler', 'datediff', 'mchar_in', 'mc_mv_icase_cmp', 'fasttruncate');
    ? column?
    - 1
    1
    1
    1
    (4 rows)

    Four ones, but there should be five! By elimination, we find that datediff is missing. Attempts to find it in the folder with postgresql and in 1C patches fail:

    snowflake local # cd / tmp / t5 /
    snowflake t5 # ls
    1c_FULL_84-0.19.2.patch pg_config.h postgresql-8.4.1C.spec postgresql.pam
    applock -1c-8.4.0.patch postgresql-1c-8.3.patch postgresql-8.4.1.tar.bz2 postgresql-perl-rpath.patch
    ecpg_config.h postgresql-8.4.1 postgresql-bashprofile postgresql-prefer-ncurses.patch
    filter -requires-perl-Pg.sh postgresql-8.4.1-1.1C.src.tar postgresql.init README.rpm-dist
    Makefile.regress postgresql-8.4.1-A4.pdf postgresql-logging.patch rpm-pgsql.patch
    snowflake t5 # fgrep -ri datediff *
    snowflake t5 #

    Found some datediff function on the Internet. Guessed at all why this is needed, and generally why all these patches are needed. Instead of adapting 1C to postgres, the developers decided to adapt postgres to make SQL look like MSSQL! So they pushed functions and types there! Oh well, we found a datediff.

    Just in case, here is her code:
    CREATE OR REPLACE FUNCTION datediff (character varying, timestamp without time zone, timestamp without time zone)
    RETURNS integer AS
    $ BODY $
    DECLARE
    arg_mode alias for $ 1;
    arg_d2 alias for $ 2;
    arg_d1 alias for $ 3;
    BEGIN
    if arg_mode = 'SECOND' then
    return date_part ('epoch', arg_d1) - date_part ('epoch', arg_d2);
    elsif arg_mode = 'MINUTE' then
    return ceil ((date_part ('epoch', arg_d1) - date_part ('epoch', arg_d2)) / 60);
    elsif arg_mode = 'HOUR' then
    return ceil ((date_part ('epoch', arg_d1) - date_part ('epoch', arg_d2)) / 3600);
    elsif arg_mode = 'DAY'

    elsif arg_mode = 'WEEK' then
    return ceil ((cast (arg_d1 as date) - cast (arg_d2 as date)) / 7.0);
    elsif arg_mode = 'MONTH' then
    return 12 * (date_part ('year', arg_d1) - date_part ('year', arg_d2))
    + date_part ('month', arg_d1) - date_part ('month', arg_d2);
    elsif arg_mode = 'QUARTER' then
    return 4 * (date_part ('year', arg_d1) - date_part ('year', arg_d2))
    + date_part ('quarter', arg_d1) - date_part ('quarter', arg_d2);
    elsif arg_mode = 'YEAR' then
    return (date_part ('year', arg_d1) - date_part ('year', arg_d2));
    end if;
    END
    $ BODY $
    LANGUAGE plpgsql VOLATILE
    COST 100;

    Attention! Formatting the text on the Habrice replaces the minus sign with a hyphen. Copy the text of the procedure first into a text editor, and there in bulk replace the long hyphen with a short minus, otherwise the postgres will swear :-)

    Count the ones again :-)

    template1 = # select 1 from pg_proc where proname in ('plpgsql_call_handler', 'datediff', 'mchar_in', 'mc_mv_icase_cmp', 'fasttruncate');
    ? column?
    - 1
    1
    1
    1
    1
    (5 rows)

    Like cheers! But there it was :-) 10 times we recount the functions, there are five of them. And we have five! And 1C at startup still says that our base is bad, and it doesn’t go any further. We look again at all (a couple of pieces in total!) The initial requests when configuring 1C. We understand that all she can do is count the ones! But there are 5 of them !!!
    We achieved this result in 3 hours after downloading the archive from the 1C site. Then began the dances with a tambourine. Numerous rebuilds, attempts to download different versions, distributions from Etersoft ... But the output is the same: five functions, five units. Though head against the wall.
    And then the idea came up to perform this procedure on a working configuration ... We send the text by mail to a friend, he feeds it to the database, and sends the result:

    demo = # select 1 from pg_proc where proname in ('plpgsql_call_handler', 'datediff', 'mchar_in', 'mc_mv_icase_cmp', 'fasttruncate');
    ? column?
    - 1
    1
    1
    1
    1
    1
    (6 rows)

    We have a quiet stupor. Thoughts move with difficulty. Someone notices that apparently there are more than one procedure :-))
    It is logical. Using the mail method, we find out that plpgsql_call_handler is in two instances. Calling select * from pg_proc where proname = 'plpgsql_call_handler' on the demo machine produced two identical records, except for the pronamespace field.
    Further, long attempts by hands to stick a record into the pg_proc system table, cursing at the fed values.
    Again stupor. The reason is clear what to do - no.
    And here we type one word in Yandex: plpgsql_call_handler (before that, different combinations tried to google). In the first place is the site www.samag.ru/art/10.2004/2004_04.html , where it says:

    Next, we need to make sure that the procedural language PL / pgSQL is available in the created database. To do this, run the following command:

    select * from pg_language;
    If plpgsql is not found in the result, then run the following command from the shell of your OS:

    $ createlang –U pgsql plpgsql eshop
    The –U option sets the user, the database administrator, on behalf of whom this operation will be performed.

    Language support can also be enabled from the client program (for example, psql) by executing the following two commands:

    create function plpgsql_call_handler ()
    returns language_handler
    as '$ libdir / plpgsql', 'plpgsql_call_handler'
    language 'c';
    create trusted procedural language 'plpgsql'
    handler plpgsql_call_handler;
    The first of them creates a handler function based on a library function, the second - the PL / pgSQL language itself, based on the handler created above.


    And then it dawned on - to create the same procedure again :-P
    Fill the procedure into the database:
    create function plpgsql_call_handler ()
    returns language_handler
    as '$ libdir / plpgsql', 'plpgsql_call_handler'
    language 'c';

    template1 = # create function plpgsql_call_handler ()
    template1- # returns language_handler
    template1- # as '$ libdir / plpgsql', 'plpgsql_call_handler'
    template1- # language 'c';
    CREATE FUNCTION
    template1 = # select * from pg_proc where proname = 'plpgsql_call_handler';
    proname | pronamespace | proowner | prolang | procost | prorows | provariadic | proisagg | proiswindow | prosecdef | proisstrict | proretset | provolatile | pronargs | pronargdefaults | prorettype | proargtypes | proallargtypes | proargmodes | proargnames | proargdefaults | prosrc | probin | proconfig | proacl
    ---------------------- + -------------- + ---------- + - -------- + --------- + --------- + ------------- + ------- --- + ------------- + ----------- + ------------- + ------ ----- + ------------- + ---------- + ----------------- + - ----------- + ------------- + ---------------- + ------- ------ + ------------- + ---------------- + ------------ ---------- + ----------------- + ----------- + --------
    plpgsql_call_handler | 11 | 10 | 13 | 1 | 0 | 0 | f | f | f | f | f | v | 0 | 0 | 2280 | | | | | | plpgsql_call_handler | $ libdir / plpgsql | |
    plpgsql_call_handler | 2200 | 10 | 13 | 1 | 0 | 0 | f | f | f | f | f | v | 0 | 0 | 2280 | | | | | | plpgsql_call_handler | $ libdir / plpgsql | |
    (2 rows)

    Here they are :-)
    Check:

    template1 = # select 1 from pg_proc where proname in ('plpgsql_call_handler', 'datediff', 'mchar_in', 'mc_mv_icase_cmp', 'fasttruncate');
    ? column?
    - 1
    1
    1
    1
    1
    1
    (6 rows)

    Final - create a base from template1:

    template1 = # create database enterprise;
    CREATE DATABASE

    And now we start 1C and set on enterprise ... Oh miracle! The database was created, and 1C flooded the entire structure.

    And now the question. Why do 1C programmers have two identical procedures in the database ???
    :-)))

    PS Then, when we played, we removed the datediff, found that it depends on plpgsql_call_handler:
    template1 = # drop language plpgsql;
    ERROR: cannot drop language plpgsql because other objects depend on it DETAILS
    : function datediff (character varying, timestamp without time zone, timestamp without time zone) depends on language plpgsql
    But still they didn’t understand where it comes from in the assembled distributions of Ethersof or 1C :-))

    Also popular now: