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: 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

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: 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

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

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: >

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

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: 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

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

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

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

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

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

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

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

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

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

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] >

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

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

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

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

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: 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

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

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

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 --

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

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

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

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:

Re: Composite type storage overhead

> 3. The value is logically defined as a 128-bit integer, that is in > itself a compound value split into a few "bit groups". Extracting > these parts can be done by simple (and supposedly efficient) bitwise > operators when stored as integer, but becomes much more cumbersome > with UUID, I guess.