PostgreSQL manual build for 1C 8.2, or the easy way is not for us!
The article describes the manual build of the PostgreSQL database for 1C under Gentoo Linux.
I will not talk about all the dead-end branches of our labyrinth assembly postgresa for 1C. I will describe only what is not on the Internet anywhere - what we have smoked by the method of scientific spear. Despite the abundance of postgres installation manuals for 1C, none of them solved our main rakes, 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 have any RPM packages. It was not possible to install the 1C package in the rpm format for Gentoo (for a long time to explain why), so download src.rpm and automatically compile it using rpmbuild disappeared. We had the only opportunity - to transfer rpm2tgz from rpm format to a simple tar.gz archive and unpack it, which we did. And below 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. Downloading the package with all 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. We carry out: 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 best to do it in a clean folder. Also immediately unpack the 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 study it for a very long time :-) It describes everything that rpmbuild would have done (if everything was so simple!). We find the section where the patches are superimposed, and in the same order we begin to do it (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 - the jokes are gone, the patch is not applied! And it is in the official delivery. We never found that it changes the rpm spec so that such a patch overlaps. Fortunately, the patch is small and straightforward, it just changes the default configuration options, so we will return after installation and fix the config with handles. In the meantime, scored on it and go on.
patch -p0 <../applock-1c-8.4.0.patch
5. Further we study the build rpm spec section, and look at what parameters configure is invoked with. It's hard to believe that 1C will use perl, python, or tcl (and we are raising the base exclusively for 1C, we don’t want to keep our other bases in the 1C developers database, let them live in the normal original postgre), so these options are simply ignored. Total that we used:
./configure --disable-rpath --enable-nls --disable-integer-datetimes --enable-thread-safety
6. make && make install. Post root will be copied to / usr / local / pgsql.
Next, you need to install additional libraries from the contrib folder. Remember, one patch did not overlap? He adds 3 additional lines from 1C to contrib / Makefile: mchar, fulleq and fasttrun. To build mchar, you need an additional component ICU version 3.8. Therefore, we 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 will be set relative to / usr / local / icu. But we will only need a library that is connected during execution, so after installing the post-posts we will get this folder.
Let's go back to the contrib build.
cd /tmp/t5/postgresql-8.4.1/
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 in the SHLIB_LINK at the very bottom in the same file:
SHLIB_LINK + = -L / usr / local / icu / lib -licuuc -l $ (ICUNAME) -Wl, -rpath, '$$ ORIGIN'
8. Then add three 1C components to the WANTED_DIRS in the contrib / Makefile file (line 38, see v8.1c.ru/overview/postgresql_patches/8-4-1/postgresql-1c-8.4.patch ) with handles :
vacuumlo \
mchar \
fulleq \
fasttrun
9. Collect contrib:
make -C contrib && make -C contrib install
10. Use pens to copy the required ICU libraries to the folder with the posts:
cp -a / usr / local / icu / lib / * / usr / local / pgsql / lib /
rm -rf / usr / local / icu
11. Postrges compiled and installed in / usr / local / pgsql. Configs will not be ruled yet. Create a system account under which the database 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 << EOF
export LANG = en_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 already have one post server running on the server, so for 1C we chose a different port and system account, created a bash profile running during su - pgmaster, this way 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 silently without losing the controlling terminal.
Change password superyuzru (screen taxis, 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
And now let's extinguish the postgres in the first console by CTRL + C, and it's time to edit the configs.
13. The data / pg_hba.conf file, it is necessary to change the trust in it to md5, as well as add an entry allowing access over 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 data / postgresql.conf file, you need to set the following options in it (part of a non-patch applied, part for debugging):
log_destination = 'stderr'
logging_collector = off # turn off while log generation in data / pg_log will output to console)
log_statement = 'all' # we want to see that it will issue 1C 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 = 256,000,000, or edit /etc/sysctl.conf, add kernel.shmmax = 256,000,000, and then sysctl -p.
16. And now the fun begins. Run postges again (postgres -D data -p 5434). 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 base template1:
template1 = # create language plpgsql;
CREATE LANGUAGE
template1 = #
18. Next, you need to fill 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, did make check, test, and so on. But they didn’t find how in RPM packages all this code gets into template1. 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 the mchar.so podgruzka, which in turn did not find libicu *, then you forgot about LD_LIBRARY_PATH or copied the ICUs to / usr / local / pgsql / lib, read first :-P
19. But that's not all !!! The most important traffic jam, because of which they could not move on. We set on base 1C, we look in 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: "Your database is bad, it does not fit."
We enter this query from the console:
template1 = # select 1 from pg_proc where proname in ('plpgsql_call_handler', 'datediff', 'mchar_in', 'mc_mv_icase_cmp', 'fasttruncate');
? column?
- one
one
one
one
(4 rows)
Four ones, and it seems like there should be five! By the exception method, we find out that there is no datediff. Attempts to find it in the folder with postgresql and in 1C patches do not lead to success:
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 on the Internet some datediff function. We guessed why it is needed at all, and why all these patches are needed at all. Instead of adapting 1C to postrgues, the developers decided to adapt postgres so that SQL looks like MSSQL! Here and there there functions and types! Well, okay, we found datediff.
Just in case, here is its code:
Attention! Text formatting on Habré replaces the minus sign with a hyphen. Copy the text of the procedure first into a text editor, and there massively replace a long hyphen with a short minus, otherwise the postgres will swear :-)
Count again edinichki :-)
template1 = # select 1 from pg_proc where proname in ('plpgsql_call_handler', 'datediff', 'mchar_in', 'mc_mv_icase_cmp', 'fasttruncate');
? column?
- one
one
one
one
one
(5 rows)
Like cheers! But it was not there :-) We recalculate the functions 10 times, five of them. And we have five! A 1C at startup still says that we have a bad base, and no longer goes. We look again all (a couple of pieces of everything!) Initial requests when configuring 1C. We understand that all she can do is count the units! But there are 5 of them !!!
We achieved this result in 3 hours after downloading the archive from 1C. Next began dancing with a tambourine. Numerous rebuilds, attempts to download different versions, distros from Etersoft ... But the output is one: five functions, five units. Though about the wall with his head.
And then I got the idea to perform this procedure on the 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?
- one
one
one
one
one
one
(6 rows)
We have a quiet stupor. Thoughts move with difficulty. Someone notices that apparently, some procedures more than one :-))
Is logical. By the method of poking by mail, we find out that plpgsql_call_handler is in two copies. The call select * from pg_proc where proname = 'plpgsql_call_handler' on the demo machine issued two identical entries, with the exception of the pronamespace field.
Further, many attempts by hands to insert an entry into the pg_proc system table, swearing 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, various combinations were tried to google). In the first place is the site www.samag.ru/art/10/2004/10/2004_07.html , where it is written:
And then it dawned on - to create the same procedure again :-P
Fill the procedure in the database:
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?
- one
one
one
one
one
one
(6 rows)
Final - create a database from template1:
template1 = # create database enterprise;
CREATE DATABASE
And now we run 1C and set it on the enterprise ... lo and behold! The database was created, and 1C flooded the entire structure.
And now the question. Why do programmers 1C in the database have two identical procedures ???
:-)))
PS Then, when they were playing, they deleted datediff, they found that it depends on plpgsql_call_handler:
template1 = # drop language plpgsql;
ERROR: cannot fall language plpgsql because other objects depend on it
DETAILED: 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 Etersoft or 1C distributions :-))
I will not talk about all the dead-end branches of our labyrinth assembly postgresa for 1C. I will describe only what is not on the Internet anywhere - what we have smoked by the method of scientific spear. Despite the abundance of postgres installation manuals for 1C, none of them solved our main rakes, 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 have any RPM packages. It was not possible to install the 1C package in the rpm format for Gentoo (for a long time to explain why), so download src.rpm and automatically compile it using rpmbuild disappeared. We had the only opportunity - to transfer rpm2tgz from rpm format to a simple tar.gz archive and unpack it, which we did. And below 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. Downloading the package with all 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. We carry out: 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 best to do it in a clean folder. Also immediately unpack the 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 study it for a very long time :-) It describes everything that rpmbuild would have done (if everything was so simple!). We find the section where the patches are superimposed, and in the same order we begin to do it (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 - the jokes are gone, the patch is not applied! And it is in the official delivery. We never found that it changes the rpm spec so that such a patch overlaps. Fortunately, the patch is small and straightforward, it just changes the default configuration options, so we will return after installation and fix the config with handles. In the meantime, scored on it and go on.
patch -p0 <../applock-1c-8.4.0.patch
5. Further we study the build rpm spec section, and look at what parameters configure is invoked with. It's hard to believe that 1C will use perl, python, or tcl (and we are raising the base exclusively for 1C, we don’t want to keep our other bases in the 1C developers database, let them live in the normal original postgre), so these options are simply ignored. Total that we used:
./configure --disable-rpath --enable-nls --disable-integer-datetimes --enable-thread-safety
6. make && make install. Post root will be copied to / usr / local / pgsql.
Next, you need to install additional libraries from the contrib folder. Remember, one patch did not overlap? He adds 3 additional lines from 1C to contrib / Makefile: mchar, fulleq and fasttrun. To build mchar, you need an additional component ICU version 3.8. Therefore, we 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 will be set relative to / usr / local / icu. But we will only need a library that is connected during execution, so after installing the post-posts we will get this folder.
Let's go back to the contrib build.
cd /tmp/t5/postgresql-8.4.1/
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 in the SHLIB_LINK at the very bottom in the same file:
SHLIB_LINK + = -L / usr / local / icu / lib -licuuc -l $ (ICUNAME) -Wl, -rpath, '$$ ORIGIN'
8. Then add three 1C components to the WANTED_DIRS in the contrib / Makefile file (line 38, see v8.1c.ru/overview/postgresql_patches/8-4-1/postgresql-1c-8.4.patch ) with handles :
vacuumlo \
mchar \
fulleq \
fasttrun
9. Collect contrib:
make -C contrib && make -C contrib install
10. Use pens to copy the required ICU libraries to the folder with the posts:
cp -a / usr / local / icu / lib / * / usr / local / pgsql / lib /
rm -rf / usr / local / icu
11. Postrges compiled and installed in / usr / local / pgsql. Configs will not be ruled yet. Create a system account under which the database 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 << EOF
export LANG = en_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 already have one post server running on the server, so for 1C we chose a different port and system account, created a bash profile running during su - pgmaster, this way 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 silently without losing the controlling terminal.
Change password superyuzru (screen taxis, 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
And now let's extinguish the postgres in the first console by CTRL + C, and it's time to edit the configs.
13. The data / pg_hba.conf file, it is necessary to change the trust in it to md5, as well as add an entry allowing access over 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 data / postgresql.conf file, you need to set the following options in it (part of a non-patch applied, part for debugging):
log_destination = 'stderr'
logging_collector = off # turn off while log generation in data / pg_log will output to console)
log_statement = 'all' # we want to see that it will issue 1C 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 = 256,000,000, or edit /etc/sysctl.conf, add kernel.shmmax = 256,000,000, and then sysctl -p.
16. And now the fun begins. Run postges again (postgres -D data -p 5434). 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 base template1:
template1 = # create language plpgsql;
CREATE LANGUAGE
template1 = #
18. Next, you need to fill 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, did make check, test, and so on. But they didn’t find how in RPM packages all this code gets into template1. 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 the mchar.so podgruzka, which in turn did not find libicu *, then you forgot about LD_LIBRARY_PATH or copied the ICUs to / usr / local / pgsql / lib, read first :-P
19. But that's not all !!! The most important traffic jam, because of which they could not move on. We set on base 1C, we look in 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: "Your database is bad, it does not fit."
We enter this query from the console:
template1 = # select 1 from pg_proc where proname in ('plpgsql_call_handler', 'datediff', 'mchar_in', 'mc_mv_icase_cmp', 'fasttruncate');
? column?
- one
one
one
one
(4 rows)
Four ones, and it seems like there should be five! By the exception method, we find out that there is no datediff. Attempts to find it in the folder with postgresql and in 1C patches do not lead to success:
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 on the Internet some datediff function. We guessed why it is needed at all, and why all these patches are needed at all. Instead of adapting 1C to postrgues, the developers decided to adapt postgres so that SQL looks like MSSQL! Here and there there functions and types! Well, okay, we found datediff.
Just in case, here is its 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' then
return cast (arg_d1 as date) - cast (arg_d2 as date);
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! Text formatting on Habré replaces the minus sign with a hyphen. Copy the text of the procedure first into a text editor, and there massively replace a long hyphen with a short minus, otherwise the postgres will swear :-)
Count again edinichki :-)
template1 = # select 1 from pg_proc where proname in ('plpgsql_call_handler', 'datediff', 'mchar_in', 'mc_mv_icase_cmp', 'fasttruncate');
? column?
- one
one
one
one
one
(5 rows)
Like cheers! But it was not there :-) We recalculate the functions 10 times, five of them. And we have five! A 1C at startup still says that we have a bad base, and no longer goes. We look again all (a couple of pieces of everything!) Initial requests when configuring 1C. We understand that all she can do is count the units! But there are 5 of them !!!
We achieved this result in 3 hours after downloading the archive from 1C. Next began dancing with a tambourine. Numerous rebuilds, attempts to download different versions, distros from Etersoft ... But the output is one: five functions, five units. Though about the wall with his head.
And then I got the idea to perform this procedure on the 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?
- one
one
one
one
one
one
(6 rows)
We have a quiet stupor. Thoughts move with difficulty. Someone notices that apparently, some procedures more than one :-))
Is logical. By the method of poking by mail, we find out that plpgsql_call_handler is in two copies. The call select * from pg_proc where proname = 'plpgsql_call_handler' on the demo machine issued two identical entries, with the exception of the pronamespace field.
Further, many attempts by hands to insert an entry into the pg_proc system table, swearing 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, various combinations were tried to google). In the first place is the site www.samag.ru/art/10/2004/10/2004_07.html , where it is written:
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 in the result is missing, then from the shell of your OS, run the following command:
$ createlang –U pgsql plpgsql eshop
The –U option specifies the user - database administrator on whose behalf this operation will be performed.
Language support can also be enabled from a 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 the 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 in 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?
- one
one
one
one
one
one
(6 rows)
Final - create a database from template1:
template1 = # create database enterprise;
CREATE DATABASE
And now we run 1C and set it on the enterprise ... lo and behold! The database was created, and 1C flooded the entire structure.
And now the question. Why do programmers 1C in the database have two identical procedures ???
:-)))
PS Then, when they were playing, they deleted datediff, they found that it depends on plpgsql_call_handler:
template1 = # drop language plpgsql;
ERROR: cannot fall language plpgsql because other objects depend on it
DETAILED: 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 Etersoft or 1C distributions :-))
Source: https://habr.com/ru/post/107321/
All Articles