Failed to increase the restart LSN of PostgreSQL logical replication slot

Hi,When I tried to update the flush LSN position of the logical replication slot for my 11.3 database, using the command select pg_replication_slot_advance(, )I get the error:user=cdcpsqlsrc,db=db_dsn_test03,app=PostgreSQL JDBC Driver,client=172.24.42.236 DEBUG: failed to increase restart lsn: prop

Re: Composite type storage overhead

Actually, this is not such a unique idea: https://instagram-engineering.com/sharding-ids-at-instagram-1cf5a71e5a5c Thanks for the suggestion to split up the primary key into components. But even going down this way, packing the components into one superstructure (composite type) would be benefi

A very puzzling backup/restore problem

I have a very confusing isse. I am trying to backup and restre a signle table . first I dump the table. Script started on 2019-10-24 06:29:12-0400 ]0;stan@smokey: ~stan@smokey:~$ pg_dump -a --table task_instance > task_instance.dump Then I connect to the db, and verify that things are as

PGPool version 4.0.6-1

Hi All, I have a confusion about the Pgpool -ii version. Can you let the know the difference between pgpool-II-pg11-4.0.6-1 and pgpool-II-96-4.0.6-1 ? To me, it looks like it is the same version of pgpool-II i.e. 4.0.6 but compiled with postgresql 11 or postgresql 9.6. otherwise they both have t

LocalTransactionId vs txid_current

I'm exploring DTrace, and I thought that LocalTransactionId as described in would have a relationship with txid_current(), but apparently I'm not getting it: template1=# begin; template1=# select txid_current(); txid_current --

Re: A very puzzling backup/restore problem

On Thu, Oct 24, 2019 at 07:57:14AM -0500, Ron wrote: > Why did you do "\i task_instance.dump" instead of "pg_restore"? Ah, that may be the root of my problem. I had in the back of my mind that the result of a pg_dump was a free standing SQL script, but that my be exactly what is going on. I will

Re: A very puzzling backup/restore problem

On 10/24/19 3:52 AM, stan wrote: I have a very confusing isse. I am trying to backup and restre a signle table . first I dump the table. Actually you are just dumping the table data. More below. Script started on 2019-10-24 06:29:12-0400 ]0;stan@smokey: ~stan@smokey:~$ pg_dump -a --tab

Re: PGPool version 4.0.6-1

On 10/24/19 6:10 AM, Vikas Sharma wrote: Hi All, I have a confusion about the Pgpool -ii version. Can you let the know the difference between pgpool-II-pg11-4.0.6-1 and pgpool-II-96-4.0.6-1 ? To me, it looks like it is the same version of pgpool-II i.e. 4.0.6 but compiled with postgresql 11

Re: A very puzzling backup/restore problem

On Thu, Oct 24, 2019 at 07:04:11AM -0700, Adrian Klaver wrote: > On 10/24/19 3:52 AM, stan wrote: > > > > > > I have a very confusing isse. I am trying to backup and restre a signle > > table . > > > > first I dump the table. > > Actually you are just dumping the table data. > > More below. >

Re: A very puzzling backup/restore problem

On 10/24/19 7:32 AM, stan wrote: On Thu, Oct 24, 2019 at 07:04:11AM -0700, Adrian Klaver wrote: On 10/24/19 3:52 AM, stan wrote: I have a very confusing isse. I am trying to backup and restre a signle table . first I dump the table. Actually you are just dumping the table data. More below

Re: LocalTransactionId vs txid_current

Luca Ferrari writes: > I'm exploring DTrace, and I thought that LocalTransactionId as > described in > would have a relationship with txid_current(), It does not. txid_current returns a globally valid transaction ID, while local transaction

Re: Having more than one constraint trigger on a table

On 10/22/19 8:26 AM, Andreas Joseph Krogh wrote: På tirsdag 22. oktober 2019 kl. 17:12:59, skrev Adrian Klaver mailto:adrian.kla...@aklaver.com>>: [snip] No. When I sort the triggers I get: test=# create table trg_str(fld_1 varchar); CREATE TABLE test=# insert into trg_

SQL Error [0A000]: ERROR: nondeterministic collations are not supported for LIKE

Hello, Not sure if my original email was actually received or not. If it was, forgive the repost... I have a question about nondeterministic collations in PostgreSQL 12. I have created a new collation that is nondeterministic and created several columns which use this collation. Querying these

Re: Having more than one constraint trigger on a table

På torsdag 24. oktober 2019 kl. 16:59:42, skrev Adrian Klaver < adrian.kla...@aklaver.com >: On 10/22/19 8:26 AM, Andreas Joseph Krogh wrote: > På tirsdag 22. oktober 2019 kl. 17:12:59, skrev Adrian Klaver > mailto:adrian.kla...@aklaver.com>>: > > [snip] >

Search path

I just was educated on the security issues of search path. As a result I am going to define a schema for the project we are working on. I set this in postgresql.conf search_path = 'ica , "$user", public' Here is the question. Will this path be in effect for users connecting from MS Access client

Re: Search path

On 10/24/19 9:41 AM, stan wrote: I just was educated on the security issues of search path. As a result I am going to define a schema for the project we are working on. I set this in postgresql.conf search_path = 'ica , "$user", public' Here is the question. Will this path be in effect for use

Recover databases from raw files (only exists the base directory)

Hi. I have been contacted for this interesting issue: on a standalone postgresql 9.4 server someone deleted all PostgreSQL files and directories except the base directory and its content (/var/lib/pgsql/9.4-base/data/base). So, the question is: there is any chance/procedure to recover the data

Re: Lookup Primary Key of Foreign Server's Table

Yeah. Part of my confusion is due to Heroku providing a Data Links service that handles a lot of the internal details around establishing a foreign server connection, and not knowing exactly what to expect. I experimented with IMPORT FOREIGN SCHEMA in a couple of test databases and noticed that th

Re: Recover databases from raw files (only exists the base directory)

Carlos Martinez wrote: > I have been contacted for this interesting issue: on a standalone > postgresql 9.4 > server someone deleted all PostgreSQL files and directories except the base > directory > and its content (/var/lib/pgsql/9.4-base/data/base). > > So, the question is: there is any ch

Re: jsonb_set() strictness considered harmful to data

On Wed, 2019-10-23 at 13:00 -0600, Stuart McGraw wrote: > > You can only say that if you don't understand NULL (you wouldn't be alone). > > If I modify a JSON with an unknown value, the result is unknown. > > This seems very intuitive to me. > > Would you expect modifying an array value with an un

Re: SQL Error [0A000]: ERROR: nondeterministic collations are not supported for LIKE

Jeff Lanzarotta wrote: > I have a question about nondeterministic collations in PostgreSQL 12. I > have created a new collation that is nondeterministic and created several > columns which use this collation. Querying these columns works great until > I use LIKE. When I do, I get the fo

Re: SQL Error [0A000]: ERROR: nondeterministic collations are not supported for LIKE

Daniel, thanks for the reply. I believe we will just remove the collation, allow LIKE to function normally, and wait for a future patch is one is ever provided. On Thu, Oct 24, 2019 at 3:39 PM Daniel Verite wrote: > Jeff Lanzarotta wrote: > > > I have a question about nondeterministic c

Re: jsonb_set() strictness considered harmful to data

Laurenz Albe writes: > On Wed, 2019-10-23 at 13:00 -0600, Stuart McGraw wrote: >> It is less sensible with compound values where the rule can apply to >> individual scalar components. I agree that JSON can sensibly be viewed as a composite value, but ... >> And indeed that is what Postgresql do

Re: EXPLAIN BUFFERS and I/O timing accounting questions

Hi, On 2019-10-21 23:18:32 -0700, Maciek Sakrejda wrote: > I ran across an EXPLAIN plan and had some questions about some of its > details. The BUFFERS docs say > > >The number of blocks shown for an upper-level node includes those used by > all its child nodes. > > I initially assumed this woul

Re: A very puzzling backup/restore problem

On Thu, Oct 24, 2019 at 07:40:29AM -0700, Adrian Klaver wrote: > On 10/24/19 7:32 AM, stan wrote: > > On Thu, Oct 24, 2019 at 07:04:11AM -0700, Adrian Klaver wrote: > > > On 10/24/19 3:52 AM, stan wrote: > > > > > > > > > > > > I have a very confusing isse. I am trying to backup and restre a si

Re: A very puzzling backup/restore problem

On 10/24/19 2:58 PM, stan wrote: On Thu, Oct 24, 2019 at 07:40:29AM -0700, Adrian Klaver wrote: On 10/24/19 7:32 AM, stan wrote: On Thu, Oct 24, 2019 at 07:04:11AM -0700, Adrian Klaver wrote: On 10/24/19 3:52 AM, stan wrote: I have a very confusing isse. I am trying to backup and restre a

Re: A very puzzling backup/restore problem

Adrian Klaver writes: > On 10/24/19 2:58 PM, stan wrote: >> So, it appears that this means that the function cannot be found, even if it >> is in the new >> (default) schema. > The original error was not about finding the function it was about not > finding the table in the function: > psql:ta

Re: EXPLAIN BUFFERS and I/O timing accounting questions

On Thu, Oct 24, 2019 at 2:25 PM Andres Freund wrote: > Note that the buffer access stats do *not* count the number of distinct > buffers accessed, but that they purely the number of buffer > accesses. You mean, even within a single node? That is, if a node hits a block ten times, that counts as t

Re: EXPLAIN BUFFERS and I/O timing accounting questions

Hi, On 2019-10-24 16:31:39 -0700, Maciek Sakrejda wrote: > On Thu, Oct 24, 2019 at 2:25 PM Andres Freund wrote: > > Note that the buffer access stats do *not* count the number of distinct > > buffers accessed, but that they purely the number of buffer > > accesses. > > You mean, even within a si

Re: jsonb_set() strictness considered harmful to data

On 10/24/19 2:17 PM, Tom Lane wrote: Laurenz Albe writes: On Wed, 2019-10-23 at 13:00 -0600, Stuart McGraw wrote: It is less sensible with compound values where the rule can apply to individual scalar components. I agree that JSON can sensibly be viewed as a composite value, but ... And

Re: Is this a bug ?

On Wed, 23 Oct 2019 11:27:12 -0500, Ron wrote: >On 10/23/19 11:20 AM, Geoff Winkless wrote: >> On Wed, 23 Oct 2019 at 17:09, Ron wrote: >> >>> As much as I hate to say it, MSFT was right to ignore this bug in the >>> standard. >> Standards are standards for a reason. It is almost never correct

Index

I have created indexes with certain set of columns. Now I want to remove one of the column and add new column. Can this done without dropping the index?

Re: Search path

stan schrieb am 24.10.2019 um 18:41: > I just was educated on the security issues of search path. As a result > I am going to define a schema for the project we are working on. > I set this in postgresql.conf > > search_path = 'ica , "$user", public' > > Here is the question. Will this path be i