constant crashing
Hello, I am trying to load about 1.4 billion records into a postgresql table. The data is in about 100 individual CSV files which I load individually into the table using the COPY command. After the data is loaded I perform updates to the data, and this is when I get errors. Some updates work, some crash. I am using psql and an SQL script file (update.sql) to perform the updating (UPDATE table SET field=UPPER(field), etc.). The errors are all "Server closed the connection unexpectedly" The problem is that the errors are never the same and occur at different times and at different steps in the process. If I update the machine (apt update/upgrade), reboot and try again, the errors occur at different places. The errors messages and logs are not helpful and I have not been able to determine the cause. I decided to load the CSV files into temporary tables and then perform the updates on the temporary tables before moving the data to the main table. This works for most of the tables, but there are always 1 or 2 that crash when I try to update them. In my last attempt, all tables updated successfully except the last one, with about 100 million records. I tried changing these settings to 60 minutes and it still crashes. SET statement_timeout = '60min'; SET idle_in_transaction_session_timeout = '60min'; Then I tried to update the data in blocks of 100,000 records and it crashed 4 times on 4 different blocks. So I updated the first crashed block down to the a block of 10 records, until it crashed. Then I updated each of the 10 records individually to identify the record that is problemantic, but then all 10 records updated without crashing! Pure insanity! I am using a dedicated machine which is a 10 core i9 with 128 GIG of RAM and 2 x 4 TB NVMEs. There is only 1 user on this stand-alone machine, me, and the machine is not connected to the internet or a network. I have reinstalled ubuntu 22.04 server many times, wiping out the discs and starting over. Last attempts have been with ubuntu 22.04.04 and postgreSQL 16. But the errors persist. I have been working on this for just over 1 year now, documenting every step, and I am still unable to get this to work without it crashing somewhere along the way. I am beginning to wonder if postgreSQL is bi-polar. Any help would be greatly appreciated. Thank you
re: constant crashing
The full error reads:
server closed the connection expectantly
This probably means the server terminated abnormally
before or while processing the request.
error: connection to server was lost
PostgreSQL 16.2
I also believe it is a resource issue which can be rectified with a setting,
but which setting?
If you were updating 100 million records what settings would you adjust?
Here are the updates I am performing on the 100 million records:
UPDATE table SET category_modified = UPPER(category);
UPDATE table SET category_modified =
REGEXP_REPLACE(REPLACE(REPLACE(category_modified, ,'-'), '`', '-'),
'\s{2,}', ' ', 'g') WHERE AND LENGTH(category_modified)>1 AND POSITION('--' IN
category_modified)>0;
UPDATE table SET category_modified = REPLACE(category_modified,' ','-');
UPDATE table SET category_modified = CASE WHEN category_modified IS NOT NULL
THEN regexp_replace(category_modified, '[^a-zA-Z]$', '') ELSE NULL END;
UPDATE table SET category_modified = regexp_replace(category_modified, '-{2,}',
'-', 'g');
UPDATE table SET category_modified = SUBSTRING(category_modified FROM 1 FOR
LENGTH(category_modified) - 1) WHERE LENGTH(category_modified)>1 AND
category_modified LIKE '%-';
re: constant crashing
Here is an excerpt of /var/log/postgresql/postgresql-16-main.log 2024-04-14 12:17:42.321 EDT [7124] LOG: checkpoint starting: wal 2024-04-14 12:17:43.153 EDT [1227] LOG: server process (PID 7289) was terminated by signal 11: Segmentation fault 2024-04-14 12:17:43.153 EDT [1227] DETAIL: Failed process was running: UPDATE main SET category_modified = UPPER(category), sub_category=UPPER(sub_category), code1=UPPER(code1), code2=UPPER(code2); 2024-04-14 12:17:43.153 EDT [1227] LOG: terminating any other active server processes 2024-04-14 12:17:43.166 EDT [1227] LOG: all server processes terminated; reinitializing 2024-04-14 12:17:43.206 EDT [7320] LOG: database system was interrupted; last known up at 2024-04-14 12:17:42 EDT 2024-04-14 12:17:43.484 EDT [7320] LOG: database system was not properly shut down; automatic recovery in progress 2024-04-14 12:17:43.487 EDT [7320] LOG: redo starts at 1260/66400608 2024-04-14 12:17:44.985 EDT [7320] LOG: unexpected pageaddr 1260/4A7CA000 in WAL segment 00011260008C, LSN 1260/8C7CA000, offset 8167424 2024-04-14 12:17:44.986 EDT [7320] LOG: redo done at 1260/8C7C9F50 system usage: CPU: user: 1.20 s, system: 0.28 s, elapsed: 1.49 s 2024-04-14 12:17:44.999 EDT [7321] LOG: checkpoint starting: end-of-recovery immediate wait 2024-04-14 12:17:45.251 EDT [7321] LOG: checkpoint complete: wrote 16284 buffers (99.4%); 0 WAL file(s) added, 0 removed, 38 recycled; write=0.082 s, sync=0.143 s, total=0.252 s; sync files=6, longest=0.075 s, average=0.024 s; distance=626470 kB, estimate=626470 kB; lsn=1260/8C7CA048, redo lsn=1260/8C7CA048 2024-04-14 12:17:45.264 EDT [1227] LOG: database system is ready to accept connections 2024-04-14 12:28:32.526 EDT [7321] LOG: checkpoint starting: wal 2024-04-14 12:28:45.066 EDT [7321] LOG: checkpoint complete: wrote 12 buffers (0.1%); 0 WAL file(s) added, 0 removed, 33 recycled; write=12.371 s, sync=0.144 s, total=12.541 s; sync files=11, longest=0.121 s, average=0.013 s; distance=536816 kB, estimate=617505 kB; lsn=1260/CB4790B8, redo lsn=1260/AD406208 2024-04-14 12:28:45.887 EDT [7321] LOG: checkpoints are occurring too frequently (13 seconds apart) 2024-04-14 12:28:45.887 EDT [7321] HINT: Consider increasing the configuration parameter "max_wal_size". 2024-04-14 12:28:45.887 EDT [7321] LOG: checkpoint starting: wal Should I increase the max_wal_size to 2GB ?
re: constant crashing
Here is the table structure: The fields being updated are the ones that are NOT named field##. Except for "3fc" which I left as is to show that it is named differently in the unlikely chance that this would be causing problems. CREATE TABLE main ( field01 character(10) COLLATE pg_catalog."default", field02 integer, field03 character varying(100) COLLATE pg_catalog."default", field04 character varying(50) COLLATE pg_catalog."default", field05 character varying(100) COLLATE pg_catalog."default", category character varying(100) COLLATE pg_catalog."default", field07 character varying(100) COLLATE pg_catalog."default", category_modified character varying(100) COLLATE pg_catalog."default", field09 text COLLATE pg_catalog."default", field10 character varying(100) COLLATE pg_catalog."default", field11 character(1) COLLATE pg_catalog."default", "3fc" character(3) COLLATE pg_catalog."default", field12 text COLLATE pg_catalog."default", field13 text COLLATE pg_catalog."default", field14 text COLLATE pg_catalog."default", field15 text COLLATE pg_catalog."default", field16 COLLATE pg_catalog."default", sub_category character(10) COLLATE pg_catalog."default", field17 character varying(100) COLLATE pg_catalog."default", field18 character varying(100) COLLATE pg_catalog."default", field19 character varying(100) COLLATE pg_catalog."default", field20 character varying(50) COLLATE pg_catalog."default", code1 character(5) COLLATE pg_catalog."default", code2 character(10) COLLATE pg_catalog."default", field21 character varying(100) COLLATE pg_catalog."default", field22 character varying(50) COLLATE pg_catalog."default", field23 character varying(50) COLLATE pg_catalog."default", field24 character varying(50) COLLATE pg_catalog."default", field25 character varying(50) COLLATE pg_catalog."default", field26 character varying(50) COLLATE pg_catalog."default", field27 character varying(50) COLLATE pg_catalog."default", field28 character varying(50) COLLATE pg_catalog."default", field29 character varying(50) COLLATE pg_catalog."default", field31 character varying(50) COLLATE pg_catalog."default", field32 character varying(10) COLLATE pg_catalog."default", field33 varying(10) COLLATE pg_catalog."default", field34 varying(10) COLLATE pg_catalog."default", field35 varying(10) COLLATE pg_catalog."default", field36 character varying(50) COLLATE pg_catalog."default", field37 character(1) COLLATE pg_catalog."default", field38 character varying(50) COLLATE pg_catalog."default", field39 boolean, field40 boolean, field41 boolean);
re: constant crashing
To show you how bi-polar this is really becoming, I tried a work-around... I took the table called us113 with 113 million records and tried to break it down into 10 smaller tables each having about 10 million records, using the following code: \set ECHO all \set ON_ERROR_STOP on -- Create 10 new tables (us113_01 to us113_10) similar to the original table (us113) CREATE TABLE us113_01 AS SELECT * FROM ( SELECT *, ROW_NUMBER() OVER () AS row_num FROM us113) sub WHERE row_num <= 1000; CREATE TABLE us113_02 AS SELECT * FROM ( SELECT *, ROW_NUMBER() OVER () AS row_num FROM us113) sub WHERE row_num > 1000 AND row_num <= 2000; CREATE TABLE us113_03 AS SELECT * FROM ( SELECT *, ROW_NUMBER() OVER () AS row_num FROM us113) sub WHERE row_num > 2000 AND row_num <= 3000; CREATE TABLE us113_04 AS SELECT * FROM ( SELECT *, ROW_NUMBER() OVER () AS row_num FROM us113) sub WHERE row_num > 3000 AND row_num <= 4000; CREATE TABLE us113_05 AS SELECT * FROM ( SELECT *, ROW_NUMBER() OVER () AS row_num FROM us113) sub WHERE row_num > 4000 AND row_num <= 5000; CREATE TABLE us113_06 AS SELECT * FROM ( SELECT *, ROW_NUMBER() OVER () AS row_num FROM us113) sub WHERE row_num > 5000 AND row_num <= 6000; CREATE TABLE us113_07 AS SELECT * FROM ( SELECT *, ROW_NUMBER() OVER () AS row_num FROM us113) sub WHERE row_num > 6000 AND row_num <= 7000; CREATE TABLE us113_08 AS SELECT * FROM ( SELECT *, ROW_NUMBER() OVER () AS row_num FROM us113) sub WHERE row_num > 7000 AND row_num <= 8000; CREATE TABLE us113_09 AS SELECT * FROM ( SELECT *, ROW_NUMBER() OVER () AS row_num FROM us113) sub WHERE row_num > 8000 AND row_num <= 9000; CREATE TABLE us113_10 AS SELECT * FROM ( SELECT *, ROW_NUMBER() OVER () AS row_num FROM us113) sub WHERE row_num > 9000; ...and of course it crashed after creating 7 tables. 2024-04-14 15:59:12.294 EDT [1212] LOG: database system is ready to accept connections 2024-04-14 16:00:39.326 EDT [1668] postgres@lf ERROR: could not access status of transaction 3687904299 2024-04-14 16:00:39.326 EDT [1668] postgres@lf DETAIL: Could not open file "pg_xact/0DBD": No such file or directory. 2024-04-14 16:00:39.326 EDT [1668] postgres@lf STATEMENT: CREATE TABLE us113_08 AS SELECT * FROM ( SELECT *, ROW_NUMBER() OVER () AS row_num FROM us113) sub WHERE row_num > 7000 AND row_num <= 8000; This is what I am dealing with. Every turn I take to get around a problem, I get more errors. I am not sure if this makes a difference but the machine actually has 144 GIG of RAM not 128 GIG. I know that Windows may have an issue with this, but I would not think ubuntu would. But I thought I'd throw that into the mess anyway.
re: constant crashing
The CSV files are being produced by another system, a WIndows app on a Windows machine. I then copy them to a USB key and copy them onto the ubuntu machine. The data is then imported via the COPY command. COPY master (field01,field02..fieldX) FROM '/data/file.text' DELIMITER E'\t' The fields are tab delimited. But importing the data works. I can get all the data into a single table without any problems. The issue is only when I start to update the single table. And that is why I started using smaller temporary tables for each CSV file, to do the updates in the smaller tables before I move them all to a single large table. After all the data is loaded and updated, I run php programs on the large table to generate reports. All of which works well EXCEPT for performing the updates on the data. And I do not want to use perl or any outside tool. I want it all one in SQL because I am required to document all my steps so that someone else can take over, so everything needs to be as simple as possible.
Re: constant crashing
I wrote the windows app. I export all data to simple ASCII text where fields are delimited with a tab and then run the file through a UTF8 converter (convertcp_v8.3_x86). I will try the entire process on a Xeon E5-1620 and let it run during the night to see what happens. But the current i9 machine is a machine from only 4 years ago which should have no issues. On Sunday, April 14th, 2024 at 8:50 PM, Adrian Klaver wrote: > > > On 4/14/24 14:50, jack wrote: > > Reply to list also > Ccing list > > > Hello, > > I am not sure what "locale" means. > > > Go to the settings App for whatever version of Windows you are on and > search for locale. > > > The Windows app is an inhouse application which uses Actian-Zen SQL. > > The data is exported to simple ASCII in a tab delimited format similar to > > CSV. > > > And you know it is ASCII for a fact? > > > Those files are then imported into the PostgreSQL table using COPY. > > Importing the data is not an issue. > > I am able to load all the data without any problems, even into 1 table > > which ends up with about 1.2 billion records. > > But when I try to update the data in that table I get many errors, > > essentially crashes. > > > Repeating what has been asked and answered it not really going anywhere. > > > There may be some control characters (garbage) in the data but that should > > not crash postgresql, especially if it can import the data without issues. > > > Unless it does. That is the point of the questions, getting to what is > actually causing the issue. Until the problem can be boiled down to a > reproducible test case there really is not much hope of anything more > then the the 'yes you have a problem' answer. And there is a difference > between dumping data into a table and then doing an UPGRADE where the > data strings are manipulated by functions. > > > Anyway, I hope I answered your questions. > > Thanks for your help. > > > > On Sunday, April 14th, 2024 at 4:28 PM, Adrian Klaver > > adrian.kla...@aklaver.com wrote: > > > > > On 4/14/24 13:18, jack wrote: > > > > > > > The CSV files are being produced by another system, a WIndows app on a > > > > Windows machine. I then copy them to a USB key and copy them onto the > > > > ubuntu machine. The data is then imported via the COPY command. > > > > > > The app? > > > > > > The locale in use on the Windows machine? > > > > > > The locale in use in the database? > > > > > > > COPY master (field01,field02..fieldX) FROM '/data/file.text' DELIMITER > > > > E'\t' > > > > The fields are tab delimited. > > > > > > > > But importing the data works. I can get all the data into a single table > > > > without any problems. The issue is only when I start to update the > > > > single table. And that is why I started using smaller temporary tables > > > > for each CSV file, to do the updates in the smaller tables before I move > > > > them all to a single large table. > > > > > > The import is just dumping the data in, my suspicion is the problem is > > > related to using string functions on the data. > > > > > > > After all the data is loaded and updated, I run php programs on the > > > > large table to generate reports. All of which works well EXCEPT for > > > > performing the updates on the data. And I do not want to use perl or any > > > > outside tool. I want it all one in SQL because I am required to document > > > > all my steps so that someone else can take over, so everything needs to > > > > be as simple as possible. > > > > > > -- > > > Adrian Klaver > > > adrian.kla...@aklaver.com > > > -- > Adrian Klaver > adrian.kla...@aklaver.com
re: constant crashing hardware issue and thank you
It seems that the hardware may in fact be the issue. I ran the exact same process during the night on a much slower machine (4 core Xeon E5-1620 v4 32G Ram) and it worked fine. It did not crash and completed all the updates on 113 million records successfully. I will be bringing the i9 in for a checkup to find the problem. Thank you all for your help with this issue.
constant crashing hardware issue and thank you TAKE AWAY
I discovered that one of the memory sticks in the machine was damaged. Running memtest86 on the machine generated many RAM errors. This was causing the strange bi-polar errors in postgresql. The hardware technician explained that he sees this often and that there is no one cause for such problems. As I am not a hardware specialist, I never thought that RAM could cause such problems. I always assumed that the OS (ubuntu or windows) would advise me if there was ever an issue with memory. TAKE AWAY: As a result of this I will be checking the RAM on all my machines once a month or the moment a machine starts to act strange. Thanks again to all who helped with this issue.
Re: SQL statement in an error report for deferred constraint violation.
Konrad Witaszczyk wrote > Hi, > > While PQresultErrorField() from libpq allows to get context in which an > error > occurred for immediate constraints, and thus an SQL statement which caused > the > constraint violation, I cannot see any way to find out which SQL statement > caused an error in case of deferred constraints, in particular deferred > foreign > key constraints. > > Is there any way to check which SQL statement or at least which row > violated a > constraint when it's deferred? If not does anyone know why there is such > restriction? > > > Konrad > > > > signature.asc (981 bytes) > <http://www.postgresql-archive.org/attachment/6015088/0/signature.asc>; First of all, you need to locate the problem SQL by modifying log parameters. sed -ir "s/#*logging_collector.*/logging_collector= on/" $PGDATA/postgresql.conf sed -ir "s/#*log_directory.*/log_directory = 'pg_log'/" $PGDATA/postgresql.conf sed -ir "s/#*log_statement.*/log_statement= 'all'/" $PGDATA/postgresql.conf Execute this SQL and send error message in the log. regards Jack Gao -- Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html
RE: pg_dump to a remote server
> -Original Message- > From: Ron > Sent: Tuesday, April 17, 2018 7:59 AM > To: pgsql-general > Subject: pg_dump to a remote server > > We're upgrading from v8.4 to 9.6 on a new VM in a different DC. The dump > file will be more than 1TB, and there's not enough disk space on the current > system for the dump file. > > Thus, how can I send the pg_dump file directly to the new server while the > pg_dump command is running? NFS is one method, but are there others > (netcat, rsync)? Since it's within the same company, encryption is not > required. > > Or would it be better to install both 8.4 and 9.6 on the new server (can I > even install 8.4 on RHEL 6.9?), rsync the live database across and then set > up log shipping, and when it's time to cut over, do an in-place pg_upgrade? > > (Because this is a batch system, we can apply the data input files to bring > the new database up to "equality" with the 8.4 production system.) > > Thanks > > -- > Angular momentum makes the world go 'round. Hi https://www.postgresql.org/docs/current/static/backup-dump.html#BACKUP-DUMP-RESTORE ... ... The ability of pg_dump and psql to write to or read from pipes makes it possible to dump a database directly from one server to another, for example: pg_dump -h host1 dbname | psql -h host2 dbname -- Jack Gao jackg...@outlook.com
RE: Which jdk version is supported by PostgreSQL
> -Original Message- > From: vaibhav zaveri > Sent: Tuesday, April 17, 2018 2:03 PM > To: pgsql-gene...@postgresql.org; pgsql_gene...@postgresql.org > Subject: Which jdk version is supported by PostgreSQL > > Hi, > > Which jdk version is supported by PostgreSQL? > > > Regards, > Vaibhav Zaveri Hi Do you mean jdbc? postgresql supports most versions of jdbc https://jdbc.postgresql.org/download.html -- Jack Gao jackg...@outlook.com
RE: Re:Postgresql with JDK
Hi, yes, supported. > If you are using Java 8 or newer then you should use the JDBC 4.2 version. < If you are using Java 7 then you should use the JDBC 4.1 version. If you are using Java 6 then you should use the JDBC 4.0 version. If you are using a Java version older than 6 then you will need to use a JDBC3 version of the driver, which will by necessity not be current, found in Other Versions. PostgreSQL JDBC 4.2 Driver, 42.2.2 -- Jack Gao jackg...@outlook.com > -Original Message- > From: vaibhav zaveri > Sent: Tuesday, April 17, 2018 2:47 PM > To: pgsql-gene...@postgresql.org > Subject: Re:Postgresql with JDK > > Hi, > > Yes that is the link. > But is JDK 1.8 supported by PostgreSQL?? > > Regards, > Vaibhav Zaveri > > On 17 Apr 2018 12:12 p.m., "Mail Delivery Subsystem" dae...@googlemail.com <mailto:mailer-dae...@googlemail.com> > > wrote: > > > > > Address not found > > Your message wasn't delivered to pgsl-gene...@postgresql.org because the > address couldn't be found, or is unable to receive mail. > The response was: > > > 550 unknown address > > > Final-Recipient: rfc822; pgsl-gene...@postgresql.org <mailto:pgsl- > gene...@postgresql.org> > Action: failed > Status: 5.0.0 > Remote-MTA: dns; makus.postgresql.org > <http://makus.postgresql.org> . (2001:4800:1501:1::229, the server for >the domain postgresql.org <http://postgresql.org> .) > Diagnostic-Code: smtp; 550 unknown address > Last-Attempt-Date: Mon, 16 Apr 2018 23:41:11 -0700 (PDT) > > > -- Forwarded message -- > From: vaibhav zaveri <mailto:vaibhavzave...@gmail.com> > > To: pgsl-gene...@postgresql.org <mailto:pgsl- > gene...@postgresql.org> > Cc: > Bcc: > Date: Tue, 17 Apr 2018 12:11:09 +0530 > Subject: Fwd: RE: Which jdk version is supported by PostgreSQL > > Hi, > > Yes this link. But is JDK version 1.8 supported with PostgreSQL?? > > Regards, > Vaibhav Zaveri > -- Forwarded message -- > From: "Gao Jack" <mailto:jackg...@outlook.com> > > Date: 17 Apr 2018 11:58 a.m. > Subject: RE: Which jdk version is supported by PostgreSQL > To: "vaibhav zaveri" <mailto:vaibhavzave...@gmail.com> >, "pgsql-gene...@postgresql.org > <mailto:pgsql-gene...@postgresql.org> " <mailto:pgsql-gene...@postgresql.org> >, "pgsql_gene...@postgresql.org > <mailto:pgsql_gene...@postgresql.org> " <mailto:pgsql_gene...@postgresql.org> > > Cc: > > > > > -Original Message- > > From: vaibhav zaveri <mailto:vaibhavzave...@gmail.com> > > > Sent: Tuesday, April 17, 2018 2:03 PM > > To: pgsql-gene...@postgresql.org <mailto:pgsql- > gene...@postgresql.org> ; pgsql_gene...@postgresql.org > <mailto:pgsql_gene...@postgresql.org> > > Subject: Which jdk version is supported by PostgreSQL > > > > Hi, > > > > Which jdk version is supported by PostgreSQL? > > > > > > Regards, > > Vaibhav Zaveri > > Hi > > Do you mean jdbc? > > postgresql supports most versions of jdbc > > https://jdbc.postgresql.org/download.html > <https://jdbc.postgresql.org/download.html> > > -- > > Jack Gao > jackg...@outlook.com <mailto:jackg...@outlook.com> > > >
RE: pg_dump to a remote server
Hi Ron,
I have some pg_dump test result, for reference only 😊
--
[ENV]
Intel(R) Core(TM) i5-4250U CPU @ 1.30GHz | SSD 120GB | 8G memory
(PostgreSQL) 9.6.8
--
[DATA]
my database has 7.2GB of random data:
postgres=# select pg_size_pretty(pg_database_size('postgres'));
pg_size_pretty
7201 MB
(1 row)
--
[Test Results]
command |
export_time | output_size
-+-+--
pg_dump postgres > outfile.sql| 16m23s | 6.3 GB
pg_dump postgres | gzip > outfile.gz | 5m27s | 2.4 GB
pg_dump -Fc postgres > outfile.dump| 5m33s | 2.4 GB
pg_dump -Fc -Z 9 postgres > outfile.dump | 11m59s | 2.4 GB
pg_dump -Ft postgres > outfile.dump | 2m43s | 6.3 GB
pg_dump -Fd postgres -f dumpdir| 5m17s | 2.4 GB
pg_dump -Fd -j 4 postgres -f dumpdir | 2m50s | 2.4 GB
(7 rows)
--
The smaller the amount of data transmitted over the network, the better.
You could try compressed export method like gzip, -Fc, -Ft, -Fd -j 4(faster).
--
Jack Gao
jackg...@outlook.com
> -Original Message-
> From: Ron
> Sent: Tuesday, April 17, 2018 9:44 AM
> To: Adrian Klaver ; pgsql-general gene...@postgresql.org>
> Subject: Re: pg_dump to a remote server
>
>
>
> On 04/16/2018 07:18 PM, Adrian Klaver wrote:
> > On 04/16/2018 04:58 PM, Ron wrote:
> >> We're upgrading from v8.4 to 9.6 on a new VM in a different DC. The
> dump
> >> file will be more than 1TB, and there's not enough disk space on the
> >> current system for the dump file.
> >>
> >> Thus, how can I send the pg_dump file directly to the new server while
> >> the pg_dump command is running? NFS is one method, but are there
> others
> >> (netcat, rsync)? Since it's within the same company, encryption is not
> >> required.
> >
> > Maybe?:
> >
> > pg_dump -d test -U postgres -Fc | ssh aklaver@arkansas 'cat > test_cat.out'
>
> That looks promising. I could then "pg_restore -jX".
>
> --
> Angular momentum makes the world go 'round.
Duplicating data folder without tablespace, for read access
Hi -- I'm wondering whether, in my specific situation, it would be safe to copy a database cluster's data folder, and bring up the copy for read access, without copying a tablespace linked from it. My situation (described below) involves a database with a 100GB table and a 600GB table where I want to routinely clone just the 100GB table for web access. --- For context, the last discussion I've found is from 2013, in this blog post from Christophe Pettus and response from Tom Lane: https://thebuild.com/blog/2013/03/10/you-cannot-recover-from-the-loss-of-a-tablespace/ https://www.postgresql.org/message-id/19786.1367378...@sss.pgh.pa.us In that discussion, Christophe summarized the situation this way: > I would not count on it. And if it works 100% reliably now, it might not on a future version of PostgreSQL. > As Josh Berkus pointed out to my off-list, there are two competing definitions of the term "recover" in use here: > 1. In my blog post, the definition of "recover" was "bring up the database without having unusually extensive knowledge of PostgreSQL's internals." > 2. For Tom, the definition of "recover" is "bring up the database if you have appropriate knowledge of PostgreSQL's internals." > You can't recover from the lost of a tablespace per definition #1. You can per definition #2. > I'd strongly suggest that relying on definition #2, while absolutely correct, is a poor operational decision for most users. https://www.postgresql.org/message-id/FABAC7F1-3172-4B5D-8E56-0B3C579980EC%40thebuild.com --- Now here's the situation where I want to do what Christophe said not to do: :) I have a large database of text, with a 600GB table and a 100GB table connected by a join table. They both see occasional updates throughout the week. Once a week I want to "cut a release," meaning I will clone just the 100GB table and copy it to a "release" server for read-only web access. My procedure would be: - keep the 600GB table on a separate tablespace - cleanly stop postgres on both servers - copy the data folder to the release server - delete pg_tblspc/* on the release server - start postgres on both servers In local testing this seems to work -- the release server works fine, and I only get an error message if I try to access the missing tables, which is expected. But are there reasons this is going to bite me if I try it in production? I'm hoping it helps that (a) I'm only doing read access, (b) I can cleanly stop both servers before cutting a release, and (c) I'm not worried about losing data, since it's just an access copy. Alternatives I've considered: - I could pg_dump and restore, but the 100GB table has lots of indexes and I'd rather not have to reindex on the release server each week. - I could replicate with pglogical and use some sort of blue-green setup on the release server to cut a release, but this adds a lot of moving parts, especially to deal with schema migrations. Thanks for any advice you might have! -Jack
Re: Duplicating data folder without tablespace, for read access
Thanks for such quick and helpful answers! My plan sounds probably better to avoid, but if it turns out to be necessary, you all gave me some helpful avenues and things to look out for. Best, Jack On Tue, Aug 14, 2018 at 1:06 PM, Stephen Frost wrote: > Greetings, > > * Jack Cushman (jcush...@gmail.com) wrote: > > I have a large database of text, with a 600GB table and a 100GB table > > connected by a join table. They both see occasional updates throughout > the > > week. Once a week I want to "cut a release," meaning I will clone just > the > > 100GB table and copy it to a "release" server for read-only web access. > > My general recommendation to people who are thinking about something > like this is to use their restore-tests as a way to stage things (you > are testing your backups by doing a restore, right?) and then copy over > the results. > > > My procedure would be: > > > > - keep the 600GB table on a separate tablespace > > - cleanly stop postgres on both servers > > - copy the data folder to the release server > > - delete pg_tblspc/* on the release server > > - start postgres on both servers > > So, instead of that procedure, it'd be: > > - Back up the database as per usual > - Restore the database somewhere > - Run some sanity checks on the restored database > - go in and drop the table and sanitize anything else necessary > - Shut down the database and copy it into place > - OR take a new backup of the sanitized database and then restore it > into place > > Much cleaner, and tests your backup/restore process. > > Alternatively, you could just track changes to the "main" database using > triggers into an audit log and then replay the changes made to the 100GB > table into the other database. > > > In local testing this seems to work -- the release server works fine, > and I > > only get an error message if I try to access the missing tables, which is > > expected. But are there reasons this is going to bite me if I try it in > > production? I'm hoping it helps that (a) I'm only doing read access, (b) > I > > can cleanly stop both servers before cutting a release, and (c) I'm not > > worried about losing data, since it's just an access copy. > > Still, it's a hacked up and not entirely proper PG database which will > likely lead to confusion- maybe you won't be confused, but I strongly > suspect others looking at it will be, and you might run into other > issues too along the lines of what Tom mentioned (background jobs > failing and such). > > Thanks! > > Stephen >
Re: Duplicating data folder without tablespace, for read access
To follow up, ZFS snapshots (appear to) offer a great solution to the problem I posed a couple of weeks ago, and avoid any hacking around with misuse of tablespaces. My goal was to have a database with a 100GB table and a 600GB table, and to routinely and efficiently clone the 100GB table and its indexes to a cluster on another machine. The general procedure for solving this with ZFS is: - zfs snapshot the source data directory (after shutting down the database or taking appropriate steps to get a clean copy) - zfs clone to the same machine. This takes no actual disk space or time because of copy-on-write. - Run postgres using the cloned data directory and truncate unwanted tables. This still takes minimal real disk space. - zfs send the cloned data directory to the remote machine. If running repeatedly (as I am), use incremental send to avoid resending unchanged blocks. The upshot is to waste minimal time copying bits that are unwanted or haven't changed. To mix in Stephen's suggestion, do this from a backup server to exercise the backups. This blog post was helpful in figuring out how to get all that working: https://blog.2ndquadrant.com/pg-phriday-postgres-zfs/ Thanks, Jack On Tue, Aug 14, 2018 at 11:57 AM Jack Cushman wrote: > Hi -- > > I'm wondering whether, in my specific situation, it would be safe to copy > a database cluster's data folder, and bring up the copy for read access, > without copying a tablespace linked from it. My situation (described below) > involves a database with a 100GB table and a 600GB table where I want to > routinely clone just the 100GB table for web access. > > --- > > For context, the last discussion I've found is from 2013, in this blog > post from Christophe Pettus and response from Tom Lane: > > > https://thebuild.com/blog/2013/03/10/you-cannot-recover-from-the-loss-of-a-tablespace/ > https://www.postgresql.org/message-id/19786.1367378...@sss.pgh.pa.us > > In that discussion, Christophe summarized the situation this way: > > > I would not count on it. And if it works 100% reliably now, it might > not on a future version of PostgreSQL. > > > As Josh Berkus pointed out to my off-list, there are two competing > definitions of the term "recover" in use here: > > > 1. In my blog post, the definition of "recover" was "bring up the > database without having unusually extensive knowledge of PostgreSQL's > internals." > > 2. For Tom, the definition of "recover" is "bring up the database if you > have appropriate knowledge of PostgreSQL's internals." > > > You can't recover from the lost of a tablespace per definition #1. You > can per definition #2. > > > I'd strongly suggest that relying on definition #2, while absolutely > correct, is a poor operational decision for most users. > > > https://www.postgresql.org/message-id/FABAC7F1-3172-4B5D-8E56-0B3C579980EC%40thebuild.com > > --- > > Now here's the situation where I want to do what Christophe said not to > do: :) > > I have a large database of text, with a 600GB table and a 100GB table > connected by a join table. They both see occasional updates throughout the > week. Once a week I want to "cut a release," meaning I will clone just the > 100GB table and copy it to a "release" server for read-only web access. > > My procedure would be: > > - keep the 600GB table on a separate tablespace > - cleanly stop postgres on both servers > - copy the data folder to the release server > - delete pg_tblspc/* on the release server > - start postgres on both servers > > In local testing this seems to work -- the release server works fine, and > I only get an error message if I try to access the missing tables, which is > expected. But are there reasons this is going to bite me if I try it in > production? I'm hoping it helps that (a) I'm only doing read access, (b) I > can cleanly stop both servers before cutting a release, and (c) I'm not > worried about losing data, since it's just an access copy. > > Alternatives I've considered: > > - I could pg_dump and restore, but the 100GB table has lots of indexes and > I'd rather not have to reindex on the release server each week. > - I could replicate with pglogical and use some sort of blue-green setup > on the release server to cut a release, but this adds a lot of moving > parts, especially to deal with schema migrations. > > Thanks for any advice you might have! > > -Jack >
Yum repository RPM behind release
Hi Postgres 13 is out and the yum repos for the release exist, eg: https://yum.postgresql.org/13/redhat/rhel-8-x86_64/ <https://yum.postgresql.org/13/redhat/rhel-8-x86_64/>. However the repository RPM suggested at https://www.postgresql.org/download/linux/redhat/ <https://www.postgresql.org/download/linux/redhat/> (https://download.postgresql.org/pub/repos/yum/reporpms/EL-8-x86_64/pgdg-redhat-repo-latest.noarch.rpm <https://download.postgresql.org/pub/repos/yum/reporpms/EL-8-x86_64/pgdg-redhat-repo-latest.noarch.rpm>) still points at rc1. I also noticed that I didn't get rc1 for a while after the release. Is the repository RPM updated on an automatic schedule? Ideally I'd like to get db<>fiddle updated on the day of the release, do I have to switch back to Debian from CentOS to achieve that? Jack
Re: Yum repository RPM behind release
Thanks very much Devrim, that got me on the right track. > Please update the repo RPM to the *latest* version first (42.0-13 or > above). rc packages were in the testing repo. v13 packages went to the > stable repo, which I added in 42.0-13. I had 42.0-14 installed but I had to manually uninstall and re-install to get the stable repo in the list in /etc/yum.repos.d/pgdg-redhat-all.repo rpm -qa | grep pgdg rpm -e pgdg-redhat-repo-42.0-14.noarch dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-8-x86_64/pgdg-redhat-repo-latest.noarch.rpm Jack
Re: Yum repository RPM behind release
> That happens when you modify the config file Thank you, that makes sense of course — and I had to change the config file to set `enabled=1` to enable the testing repo when we first installed.
Very large table: Partition it or not?
I have a table in an analytics database (Postgres 12.3), that gathers data continuously. It is at 5B rows, with an average row size of 250 bytes. The table has five indexes, on bigint and varchar columns, all with keys of one or two columns. There are currently frequent updates and deletions, but the net change in the number of rows is continuously positive. We are rearchitecting the application to avoid the updates and deletes. I.e., the table will soon be append-only, (so vacuuming will be needed only to avoid transaction id wraparound). I know that the maximum table size is 32TB, which allows for 128B rows. Based on this calculation, and the expected growth rate (2B/year currently), we should be good for quite a while. What are the pros and cons of partitioning the table? Without partitioning, are we liable to run into trouble as this table keeps growing? I do realize that some query times will grow with table size, and that partitioning, combined with parallel query execution can address that problem. I'm more wondering about problems in maintaining tables and indexes once we have 10B, 20B, ... rows. Jack Orenstein
Btree vs. GIN
I am working on a research project involving a datatype, D, with the following characteristics: - A value of D is a variable-length binary string. - A value of D usually gives rise to a single index term, but there could occasionally be more, (up to some configurable maximum). - The index terms are int64. GIN looks like a good fit for my requirements, and I've done a little experimentation (using btree_gin) to determine that the optimizer is working as I'd hoped. So I'm going to proceed with GIN indexes, writing an extension for my datatype. But I'm wondering about btrees and GIN, in general. This discussion was pretty interesting: https://www.postgresql-archive.org/Questions-about-btree-gin-vs-btree-gist-for-low-cardinality-columns-td6088041.html . My main questions are about the remaining differences between btree and GIN indexes. With btree key deduplication in Postgres 12, one of the main differences between btrees and GIN is gone, (my understanding is that GIN has done this optimization for a long time). I think that one other major difference between the two is that GIN can handle multiple keys per row, while a btree cannot. Is there some fundamental reason why the btree cannot accommodate multiple keys per row? I think that this would have no impact on the btree structure itself. The only difference would be that the same row (ctid) could be associated with multiple keys. I guess the top-level question is this: Is it possible in principle for the btree index to subsume GIN index capabilities? Jack Orenstein
Crashing on insert to GIN index
I am defining a new type, FooBar, and trying to create a GIN index for it.
Everything is working well without the index. FooBar values are getting
into a table, and being retrieved and selected correctly. But I'm getting a
crash when I add a GIN index on a column of type FooBar.
Here is the operator class:
create operator class foobar_ops
default for type foobar using gin
as
operator 1 @@,
function 1 foobar_cmp(bigint, bigint),
function 2 foobar_item_to_keys(foobar, internal),
function 3 foobar_query_to_keys(foobar, internal, int2, internal,
internal),
function 4 foobar_match(internal, int2, anyelement, int4, internal,
internal),
function 5 foobar_partial_match(foobar, foobar, int2, internal);
Here is the postgres function for extracting keys from FooBar values:
create function foobar_item_to_keys(foobar, internal) returns internal
as '$libdir/foobar'
language C immutable strict parallel safe;
And the implementation:
Datum foobar_item_to_keys(PG_FUNCTION_ARGS)
{
FooBar* foobar = (FooBar*) DatumGetPointer(PG_GETARG_DATUM(0));
int32* n_keys = (int32*) PG_GETARG_POINTER(1);
int64_t* keys = (int64_t*) palloc(sizeof(int64_t));
*n_keys = 1;
keys[0] = foobar->key0;
PG_RETURN_POINTER(keys);
}
(Eventually there will be multiple keys, so it really does need to be a GIN
index.)
I have used ereport debugging to prove that the FooBar delivered into
foobar_item_to_keys is correct, and that the PG_RETURN_POINTER statement is
being reached.
I have been reading the Postgres docs, and comparing my code to the
examples in contrib, and cannot see what I'm doing wrong. Can anyone see a
problem in what I've described? Or point me in the right direction to debug
this problem?
Thanks.
Jack Orenstein
Re: Crashing on insert to GIN index
Thank you, the missing STORAGE clause was the problem. As for the non-standard coding: I did start out with more correct coding, and it wandered off as I tried to figure out what was causing the crash. Jack Orenstein On Sun, Jan 3, 2021 at 7:57 PM Tom Lane wrote: > Jack Orenstein writes: > > I am defining a new type, FooBar, and trying to create a GIN index for > it. > > Everything is working well without the index. FooBar values are getting > > into a table, and being retrieved and selected correctly. But I'm > getting a > > crash when I add a GIN index on a column of type FooBar. > > > Here is the operator class: > > > create operator class foobar_ops > > default for type foobar using gin > > as > > operator 1 @@, > > function 1 foobar_cmp(bigint, bigint), > > function 2 foobar_item_to_keys(foobar, internal), > > function 3 foobar_query_to_keys(foobar, internal, int2, internal, > > internal), > > function 4 foobar_match(internal, int2, anyelement, int4, > internal, > > internal), > > function 5 foobar_partial_match(foobar, foobar, int2, internal); > > Hmm, don't you want a "STORAGE bigint" clause in there? > > > And the implementation: > > > int64_t* keys = (int64_t*) palloc(sizeof(int64_t)); > > As a general rule, ignoring the conventions about how to use Datums > is a good way to cause yourself pain. It doesn't look like what > you've shown us so far is directly broken ... as long as you don't > try to run it on 32-bit hardware ... but bugs could easily be lurking > nearby. More, the fact that this code looks nothing like standard > coding for the task is not making your life easier, because you > can't easily compare what you've done to other functions. It'd be > much wiser to write this as > > Datum *keys = (Datum *) palloc(sizeof(Datum) * whatever); > > and then use Int64GetDatum() to convert your integer key > values to Datums. Yes, I'm well aware that that macro is > physically a no-op (... on 64-bit hardware ...) but you're > best advised to not rely on that, but think of Datum as a > physically distinct type. > > regards, tom lane >
Missing declaration of _PG_init()
I am writing an extension. The docs describe a _PG_init function that will be called upon loading the shared library (https://www.postgresql.org/docs/12/xfunc-c.html). I include postgres.h and fmgr.h, but on compilation, _PG_init has not been declared. Grepping the postgres source, _PG_init appears to be involved in programming language extensions, and the function is declared in plpgsql.h. Looking at various contrib modules, I see explicit declarations of _PG_init(void). Should _PG_init(void) be declared in someplace included by postgres.h or fmgr.h? Jack Orenstein
Finding memory corruption in an extension
An extension I'm creating is causing Postgres to crash, almost certainly due to memory corruption. I am using palloc0/pfree, calling SET_VARSIZE, and generally following the procedures documented here: https://www.postgresql.org/docs/12/xfunc-c.html. I am also testing my code outside of Postgres (using alloc/free instead of palloc0/pfree), and valgrind is not finding any corruption or leaks. The crash is not completely reproducible, but when it does happen, it's pretty fast -- create a table, insert a couple of rows, explain a query. (My goal is to create a GIN index on my datatype, but this crash occurs without the index.) I'm interested in advice on how to go about hunting down my problem. Something along the lines of a debugging malloc, or valgrind, for Postgres. Jack Orenstein
Static memory, shared memory
I am writing a Postgres extension, and thought that I had memory corruption, (thanks for the --enable-cassert lead). I might, but It now looks like I need to understand the use of shared memory and locking in Postgres. So I have two questions. 1) I am now guessing that my original problem is caused by relying on static memory in my extension (i.e., in the source declaring PG_MODULE_MAGIC). This static memory is almost but not quite constant -- it is initialized from _PG_init, and then never modified. I suspect that this cannot work in general (since Postgres is multi-process), but I thought it would be adequate for early development. However, I am seeing this static memory get corrupted even when there is only a single process executing the extension code (verified by examining getpid()). So the question is this: Is the use of non-constant static memory ill-advised, even assuming there is just one process relying on it? Or is it more likely that I still have run-of-the-mill memory corruption. (--enable-cassert hasn't notified me of any problems). 2) Assuming that I should be using shared memory instead of static, I am reading https://www.postgresql.org/docs/12/xfunc-c.html#id-1.8.3.13.14, and examining contrib/pg_prewarm. The xfunc-c documentation mentions RequestNamedLWLockTranche to get an array of LWLocks. But the sample code that follows calls GetNamedLWLockTranche. And the pg_prewarm code doesn't rely on an array of locks, it initializes a single lock, (which I think would be adequate for my needs). I understand the purpose of locks for obtaining and manipulating shared memory but I am confused about the correct way to proceed. I'm guessing it is safe to assume that pg_prewarm works and is a good model to follow, and that the doc may be buggy. Can someone clarify my confusion, and perhaps point me at a tutorial on correct usage of the interfaces for LWLocks and shared memory, (I haven't been able to find one). Thank you. Jack Orenstein
Understanding GIN indexes
I am building a new type, which will be indexed using a GIN index. Things are starting to work, and I am seeing queries use the index, call the partialMatch(), consistent(), and compare() functions, and return correct results. However, I am still unclear on some aspects of how partialMatch and consistent are supposed to work, (so my implementation of consistent() always sets *refresh to true). 1) The recheck logic of consistent() is unclear to me. The docs say ( https://www.postgresql.org/docs/12/gin-extensibility.html): On success, *recheck should be set to true if the heap tuple needs to be rechecked against the query operator, or false if the index test is exact. That is, a false return value guarantees that the heap tuple does not match the query; a true return value with *recheck set to false guarantees that the heap tuple does match the query; and a true return value with *recheck set to true means that the heap tuple might match the query, so it needs to be fetched and rechecked by evaluating the query operator directly against the originally indexed item. How can it ever be correct to return true and set *recheck to false? My understanding of conventional (btree) indexes is that the row needs to be retrieved, and the index condition rechecked, because the table has visibility information, and the index does not -- a key in the index might correspond to an obsolete row version. I understand visibility map optimizations, and the fact that going to the actual data page can sometimes be skipped. But that doesn't seem to be what the consistent() refetch flag is about. In other words, how can consistent() ever decide that a recheck is not necessary, since the index entry may be from an obsolete row version? Couldn't returning true and setting *recheck to false result in a false positive? 2) For partial matches, why does consistent() need to be called at all? For a given key (2nd arg), partialMatch() decides whether the key satisfies the index condition. Why is a further check by consistent() required? I think that my mental model of how GIN works must be way off. Is there a presentation or paper that explains how GIN works? Jack Orenstein
Re: Static memory, shared memory
On Sat, Jan 9, 2021 at 12:18 PM Tom Lane wrote: > Jack Orenstein writes: > > I am writing a Postgres extension, and thought that I had memory > > corruption, (thanks for the --enable-cassert lead). I might, but It now > > looks like I need to understand the use of shared memory and locking in > > Postgres. So I have two questions. > > > 1) I am now guessing that my original problem is caused by relying on > > static memory in my extension (i.e., in the source declaring > > PG_MODULE_MAGIC). This static memory is almost but not quite constant -- it > > is initialized from _PG_init, and then never modified. I suspect that this > > cannot work in general (since Postgres is multi-process), but I thought it > > would be adequate for early development. However, I am seeing this static > > memory get corrupted even when there is only a single process executing the > > extension code (verified by examining getpid()). > > Define what you mean by "corrupted". It seems highly unlikely that any > code but your own is touching this memory. Some fields have expected values, others do not. I think I have just figured out this problem, and it was indeed my own gun shooting my own foot. > > Really the big-picture question here is what are you hoping to accomplish > and why do you think this memory might need to be shared? The type I am implementing depends on looking up data in an array whose size is approximately 64k. This array needs to be computed once and for all early on, and is then consulted as the type is used. For now, I have hardwired the parameters that determine the array's contents, and the array is constructed during _PG_init. I will eventually remove this scaffolding, and compute the array contents when required, which should be a rare event. Jack Orenstein
How to find an oid that's not uesd now?
I'm adding a new index in pg, but I find this. Duplicate OIDs detected: Duplicate OIDs detected: 357 357 found 1 duplicate OID(s) in catalog data found 1 duplicate OID(s) in catalog data what sql should I run to find one?
How to get the selectStatement parse tree info?
I'm reading this book https://www.interdb.jp/pg/pgsql03.html? I'm debugging pg, but I can't get the parse tree like this: https://files.slack.com/files-pri/T0FS7GCKS-F047H5R2UKH/1.png, can you give me some ways to get that? I'm using vscode to debug, the watch info doesn't interest me, it gives me nothing,https://files.slack.com/files-pri/T0FS7GCKS-F048MD5BTME/quzk_6bk0sug60__f_0wh2l.png jack...@gmail.com
please give me select sqls examples to distinct these!
typedef enum SetOperation
{
SETOP_NONE = 0,
SETOP_UNION,
SETOP_INTERSECT,
SETOP_EXCEPT
} SetOperation;
jack...@gmail.com
Re: Re: please give me select sqls examples to distinct these!
> On Oct 25, 2022, at 7:55 AM, jack...@gmail.com wrote:
>
>
>
> typedef enum SetOperation
> {
> SETOP_NONE = 0,
> SETOP_UNION,
> SETOP_INTERSECT,
> SETOP_EXCEPT
> } SetOperation;
> jack...@gmail.com
Please use just text.
What ‘dialect’ are using? In Postgres
0: select * from table
1: select * from table union select * from table is same shape
2: select * from table join table b on Id = idb
3: select * from table except select * from tableb
can you give me a sql example to explain this?
/* * In a "leaf" node representing a VALUES list, the above fields are all * null, and instead this field is set. Note that the elements of the * sublists are just expressions, without ResTarget decoration. Also note * that a list element can be DEFAULT (represented as a SetToDefault * node), regardless of the context of the VALUES list. It's up to parse * analysis to reject that where not valid. */ List*valuesLists; /* untransformed list of expression lists */ I need to understand what this is used for? jack...@gmail.com
access method xxx does not exist
I'm trying to add a new index, but when I finish it, I use “ create index xxx_index on t1 using xxx(a); ”,it gives me access method "my_index" does not exist And I don't know where this message is from, can you grve me its position? I do like this. I add oid in pg_am.dat and pg_proc.dat for my index. And I add codes in contrib and backend/access/myindex_name, is there any other places I need to add some infos? Who can help me? jack...@gmail.com
回复: access method xxx does not exist
I'm trying to add a new index, but when I finish it, I use “ create index xxx_index on t1 using xxx(a); ”,it gives me access method "my_index" does not exist And I don't know where this message is from, can you grve me its position? I do like this. I add oid in pg_am.dat and pg_proc.dat for my index. And I add codes in contrib and backend/access/myindex_name, is there any other places I need to add some infos? Who can help me? jack...@gmail.com
Re: Re: access method xxx does not exist
On 2022-10-29 19:19:28 +0800, jack...@gmail.com wrote: > I'm trying to add a new index, but when I finish it, I use “ create index > xxx_index on t1 using xxx(a); ”,it gives me access method "my_index" does not > exist > And I don't know where this message is from, can you grve me its position? See https://www.postgresql.org/docs/current/sql-createindex.html The syntax for CREATE INDEX is CREATE INDEX ON table_name [ USING method ] ( column_name ... ) You use USING to specify the method (e.g. btree or gin), not the table and/or columns. The columns (or expressions come in parentheses after that. So if you wanted an index on column a of table t1 you would simply write: CREATE INDEX ON t1 (a); Or if you have a function xxx and you want a function based index on xxx(a) of that table: CREATE INDEX ON t1 (xxx(a)); (You can specify the name of the index, but why would you?) > I do like this. I add oid in pg_am.dat and pg_proc.dat for my index. > And I add codes in contrib and backend/access/myindex_name, is there > any other places I need to add some infos? What? Why are you doing these things? hp -- _ | Peter J. Holzer| Story must make more sense than reality. |_|_) || | | | h...@hjp.at |-- Charles Stross, "Creative writing __/ | http://www.hjp.at/ | challenge!"
Re: Re: access method xxx does not exist
Hi, On Sat, Oct 29, 2022 at 08:15:54PM +0800, jack...@gmail.com wrote: > On 2022-10-29 19:19:28 +0800, jack...@gmail.com wrote: > > I'm trying to add a new index, but when I finish it, I use “ create index > > xxx_index on t1 using xxx(a); ”,it gives me access method "my_index" does > > not > > exist You should look at the bloom contrib in postgres source tree for an example of how to write a custom index AM.
Does it equal to execute "CREATE ACCESS METHOD"?
Sorry, I open another mail list to ask this question.
When I add
"{ oid => '6015', oid_symbol => 'SPB_AM_OID',
descr => 'SPB index access method',
amname => 'spb', amhandler => 'spbhandler', amtype => 'i' },"
in pg_am.dat
and add
"{ oid => '388', descr => 'spb index access method handler',
proname => 'spbhandler', provolatile => 'v',
prorettype => 'index_am_handler', proargtypes => 'internal',
prosrc => 'spbhandler' }," in pg_proc.dat,
so when I use the make install && cd contrib;make install;
whether it equals to execute create access method?
by the way, I've added the spb codes in src/access/spb, so don't worry about
the spbhandler.
And Sorry for the another mail "access method xxx does not exist", you suggest
me add new
Am Index in contrib, But I need to modify gist to spb, so that's not my
require. And I need to know
add those in pg_proc.dat and pg_am.dat, if it won't create access method for
spb, what else I need to
do?
--
jack...@gmail.com
Re: Does it equal to execute "CREATE ACCESS METHOD"?
Sorry, I open another mail list to ask this question.
When I add
"{ oid => '6015', oid_symbol => 'SPB_AM_OID',
descr => 'SPB index access method',
amname => 'spb', amhandler => 'spbhandler', amtype => 'i' },"
in pg_am.dat
and add
"{ oid => '388', descr => 'spb index access method handler',
proname => 'spbhandler', provolatile => 'v',
prorettype => 'index_am_handler', proargtypes => 'internal',
prosrc => 'spbhandler' }," in pg_proc.dat,
so when I use the make install && cd contrib;make install;
whether it equals to execute create access method?
by the way, I've added the spb codes in src/access/spb, so don't worry about
the spbhandler.
And Sorry for the another mail "access method xxx does not exist", you suggest
me add new
Am Index in contrib, But I need to modify gist to spb, so that's not my
require. And I need to know
add those in pg_proc.dat and pg_am.dat, if it won't create access method for
spb, what else I need to
do?
--
jack...@gmail.com
Re: Re: Does it equal to execute "CREATE ACCESS METHOD"?
"jack...@gmail.com" writes:
> When I add
> "{ oid => '6015', oid_symbol => 'SPB_AM_OID',
> descr => 'SPB index access method',
> amname => 'spb', amhandler => 'spbhandler', amtype => 'i' },"
> in pg_am.dat
> and add
> "{ oid => '388', descr => 'spb index access method handler',
> proname => 'spbhandler', provolatile => 'v',
> prorettype => 'index_am_handler', proargtypes => 'internal',
> prosrc => 'spbhandler' }," in pg_proc.dat,
> so when I use the make install && cd contrib;make install;
> whether it equals to execute create access method?
Did you run initdb afterwards? What you describe here should
result in an updated postgres.bki file, but that isn't the
same as catalog entries in a live database.
> And Sorry for the another mail "access method xxx does not exist", you
> suggest me add new
> Am Index in contrib, But I need to modify gist to spb, so that's not my
> require. And I need to know
> add those in pg_proc.dat and pg_am.dat, if it won't create access method for
> spb, what else I need to
> do?
To be very blunt, it doesn't sound to me that your skills with
Postgres are anywhere near up to the task of writing a new
index access method. You should start with some less-ambitious
project to gain some familiarity with the code base.
regards, tom lane
Re: Re: Does it equal to execute "CREATE ACCESS METHOD"?
thanks for your advice, I realize my problems, can you give me some materials
like some study routine for pg-internal?
--
jack...@gmail.com
>"jack...@gmail.com" writes:
>> When I add
>> "{ oid => '6015', oid_symbol => 'SPB_AM_OID',
>> descr => 'SPB index access method',
>> amname => 'spb', amhandler => 'spbhandler', amtype => 'i' },"
>> in pg_am.dat
>> and add
>> "{ oid => '388', descr => 'spb index access method handler',
>> proname => 'spbhandler', provolatile => 'v',
>> prorettype => 'index_am_handler', proargtypes => 'internal',
>> prosrc => 'spbhandler' }," in pg_proc.dat,
>> so when I use the make install && cd contrib;make install;
>> whether it equals to execute create access method?
>
>Did you run initdb afterwards? What you describe here should
>result in an updated postgres.bki file, but that isn't the
>same as catalog entries in a live database.
>
>> And Sorry for the another mail "access method xxx does not exist", you
>> suggest me add new
>> Am Index in contrib, But I need to modify gist to spb, so that's not my
>> require. And I need to know
>> add those in pg_proc.dat and pg_am.dat, if it won't create access method for
>> spb, what else I need to
>> do?
>
>To be very blunt, it doesn't sound to me that your skills with
>Postgres are anywhere near up to the task of writing a new
>index access method. You should start with some less-ambitious
>project to gain some familiarity with the code base.
>
> regards, tom lane
there is no an example in reloptions.c for string?
jack...@gmail.com
--
here is the codes for pg16.
static relopt_string stringRelOpts[] =
{
/* list terminator */
{{NULL}}
};
And I add my string type arguments here, it can't work well.
When I debug, it comes to func allocateReloptStruct, at the
code line "size += optstr->fill_cb(val, NULL);", it gives me segment
fault. what else do I need to add? can you give me an example?
modify planner codes, get failed
jack...@gmail.com -- Hello, I'm trying to modify pg codes for my personal project. And after I finish modify planner, I get this. postgres=# create table tt(a int); CREATE TABLE postgres=# \d tt ERROR: btree index keys must be ordered by attribute the patches are below 0001-finish-planner-modify.patch Description: Binary data 0003-fix-limit-and-count-bugs.patch Description: Binary data
How to repair my plan modify error?
jacktby(at)gmail(dot)com -- Hello, I'm trying to modify pg codes for my personal project. And after I finish modify planner, I get this. postgres=# create table tt(a int); CREATE TABLE postgres=# \d tt ERROR: btree index keys must be ordered by attribute here are the patches of my modifies. 0001-finish-planner-modify.patch Description: Binary data 0003-fix-limit-and-count-bugs.patch Description: Binary data
what kind of hash algorithm is used by hash_bytes()?
jack...@gmail.com -- I can't understand the hash_bytes() func in src/backend/access/hash/hashfunc.c, it's published by a paper or others? Can you give me some materials to study it in depth?
How could I elog the tupleTableSlot to the fronted terminal?
For example, I use "insert into t values(1)"; and I 'll get a tupleTableSlot, And Now I want to get the real data , that's 1, and then use elog() func to print it. Could you give me some codes to realize that? futhermore, what If the data type is text or other types? What do I need to change? -- jack...@gmail.com
Re: Re: How could I elog the tupleTableSlot to the fronted terminal?
>On 2023-Jan-30, jack...@gmail.com wrote: > >> For example, I use "insert into t values(1)"; and I 'll get a tupleTableSlot, >> >> And Now I want to get the real data , that's 1, and then use elog() func >> to print it. Could you give me some codes to realize that? futhermore, >> what If the data type is text or other types? What do I need to change? > >Maybe have a look at the 'debugtup()' function. It doesn't do exactly >what you want, but it may inspire you to write the code you need. > >-- >Álvaro Herrera 48°01'N 7°57'E — https://www.EnterpriseDB.com/ >"All rings of power are equal, >But some rings of power are more equal than others." > (George Orwell's The Lord of the Rings) I use the debugtup to print, and I find out there are "printf", it doesn't print anything to the terminal. I need to know how to use this debugtup func. I think I use it as a mistake -- jack...@gmail.com
How to create a new operator inpg for spec data type?
I need to create a new operator like '<->' and its syntax is that text1 <-> text2, for the usage like this: 'a' <-> 'b' = 'a1b1', so how could I realize this one? Can you give me some exmaples. -- jack...@gmail.com
How to write a new tuple into page?
Hi, I'm trying to construct a new tuple type, that's not heaptuple, When I get a tupleTableSlot, I will get data info from it and the I will constuct a new tuple, and now I need to put it into a physical page, how should I do? -- jack...@gmail.com
Give me details of some attributes!!
here are the source codes from src/include/access/htup_details.h. /* * information stored in t_infomask: */ #define HEAP_HASNULL 0x0001 /* has null attribute(s) */ #define HEAP_HASVARWIDTH 0x0002 /* has variable-width attribute(s) */ #define HEAP_HASEXTERNAL 0x0004 /* has external stored attribute(s) */ #define HEAP_HASOID_OLD 0x0008 /* has an object-id field */ #define HEAP_XMAX_KEYSHR_LOCK 0x0010 /* xmax is a key-shared locker */ #define HEAP_COMBOCID 0x0020 /* t_cid is a combo CID */ #define HEAP_XMAX_EXCL_LOCK 0x0040 /* xmax is exclusive locker */ #define HEAP_XMAX_LOCK_ONLY 0x0080 /* xmax, if valid, is only a locker */ And I can't understand these attrs: 1. external stored attribute(s), what is this? can you give a create statement to show me? 2. xmax is a key-shared locker/exclusive locker/only a locker, so how you use this? can you give me a scenario? let me try to explain it: if there is a txn is trying to read this heaptuple, the HEAP_XMAX_KEYSHR_LOCK bit will be set to 1. if there is a txn is trying to delete/update this heaptuple, the HEAP_XMAX_EXCL_LOCK bit will be set to 1. but for HEAP_XMAX_LOCK_ONLY, I can't understand. And another thought is that these three bit can have only one to be set 1 at most. 3. t_cid is a combo CID? what's a CID? give me an example please. -- jack...@gmail.com
what's hsitoric MVCC Snapshot?
Here are the comments in src/include/utils/snapshot.h. /* * For normal MVCC snapshot this contains the all xact IDs that are in * progress, unless the snapshot was taken during recovery in which case * it's empty. For historic MVCC snapshots, the meaning is inverted, i.e. * it contains *committed* transactions between xmin and xmax. * * note: all ids in xip[] satisfy xmin <= xip[i] < xmax */ TransactionId *xip; I can't understand the historic MVCC snapshots? can you give me a scenario to describe this? jack...@gmail.com
How does pg index page optimize dead tuples?
As far as I know, when a index page is full, if you insert a new tuple here, you will split it into two pages. But pg won't delete the half tuples in the old page in real. So if there is another tuple inserted into this old page, will pg split it again? I think that's not true, so how it solve this one? please give me a code example,thanks. jack...@gmail.com
SPI Interface to Call Procedure with Transaction Control Statements
Hi All, In PG-11, procedures were introduced. In the pg_partman tool, a procedure named run_maintenance_proc was developed to replace run_maintenance function. I was trying to call this procedure in pg_partman with SPI_execute() interface and this is the command being executed: CALL "partman".run_maintenance_proc(p_analyze := true, p_jobmon := true) I received the following error: 2019-01-02 20:13:04.951 PST [26446] ERROR: invalid transaction termination 2019-01-02 20:13:04.951 PST [26446] CONTEXT: PL/pgSQL function partman.run_maintenance_proc(integer,boolean,boolean,boolean) line 45 at COMMIT Apparently, the transaction control command 'COMMIT' is not allowed in a procedure CALL function. But I can CALL this procedure in psql directly. According to the documentation of CALL, "If CALL is executed in a transaction block, then the called procedure cannot execute transaction control statements. Transaction control statements are only allowed if CALL is executed in its own transaction." Therefore, it looks like that SPI_execute() is calling the procedure within a transaction block. So my question is that is there any SPI interface that can call a procedure with transaction control commands? (I tried to use SPI_connect_ext(SPI_OPT_NONATOMIC) to establish a nonatomic connection but it doesn't help.) Thanks, Jiayi Liu
RE: PostgreSQL debug log doesn't record whole procedure(from receiving request to sending response)
"Tom Lane" writes: > We can see from this that the server spent 10 seconds in CommitTransaction, > so the question is what took so long. I'd wonder first about end-of-transaction triggers (have you got foreign keys on that table? maybe an event trigger?), and then second about delays in writing/fsyncing WAL (what's the underlying storage? do you have synchronous_commit replication turned on?). We have the same as your second suspicion. Delays in fsyncing WAL as the backend ceph cluster storage and we see there was significant latency increased during that time. Yes. the synchronous_commit is on. fsync | on | Forces synchronization of updates to disk. synchronous_commit | on | Sets the current transaction's synchronization level. > [ shrug... ] Sure, we could put an elog(DEBUG) after every line of code in > the server, and then high-level debugging logs would be even more impossibly > voluminous than they are now. I'd say the existing logging gave you plenty > of clue where to look. Actually, do not need to write log for every line code. The last debug log we can see is for CommitTransaction which state is INPROGRESS. But we can't see when CommitTransaction state is DONE/COMPLETE from the debug log which I think this kind of log is valuable which can help to identify where the delay comes, from server or client from the postgres server debug log only shall be enough without to monitoring and analyzing the TCP message. [2701833-618d1b70.293a09-173273] 2021-11-13 22:25:58.051 GMT <169.254.0.21 UPDATE> DEBUG: 0: CommitTransaction(1) name: unnamed; blockState: STARTED; state: INPROGRESS, xid/subid/cid: 23280/1/1 [2701833-618d1b70.293a09-173274] 2021-11-13 22:25:58.051 GMT <169.254.0.21 UPDATE> LOCATION: ShowTransactionStateRec, xact.c:5333 Best Regards It always takes longer than you expect, even when you take into account -- Yan-Jack Chen (陈雁) Tel: +8613957141340 Addr: No.567 XinCheng Rd, Binjiang District, Hangzhou, China, 310053 -Original Message- From: Tom Lane Sent: 2021年11月15日 23:09 To: Chen, Yan-Jack (NSB - CN/Hangzhou) Cc: pgsql-gene...@postgresql.org Subject: Re: PostgreSQL debug log doesn't record whole procedure(from receiving request to sending response) "Chen, Yan-Jack (NSB - CN/Hangzhou)" writes: > We recently encounter one issue about PostgreSQL ODBC client doesn¡¯t > receive response from PostgreSQL server in time (client set 5 seconds > timeout) occasionally (1 or 2 times per 24 hours). Both PostgreSQL and its > client are deployed in VM against. It took us days to debug where cause the > timeout. We enable PostgreSQL server debug log via below configuration. We can see from this that the server spent 10 seconds in CommitTransaction, so the question is what took so long. I'd wonder first about end-of-transaction triggers (have you got foreign keys on that table? maybe an event trigger?), and then second about delays in writing/fsyncing WAL (what's the underlying storage? do you have synchronous_commit replication turned on?). > This mail is to ask why PostgreSQL debug log doesn¡¯t really include the > response message delay which may cause misleading why troubleshooting. It > looks to me the debug log doesn¡¯t record the whole procedure. If there are > some developer options include the missing part but we didn¡¯t enable? [ shrug... ] Sure, we could put an elog(DEBUG) after every line of code in the server, and then high-level debugging logs would be even more impossibly voluminous than they are now. I'd say the existing logging gave you plenty of clue where to look. regards, tom lane
