Re: Is there a significant difference in Memory settings between 9.5 and 12

On Monday, May 11, 2020, David G. Johnston wrote: > Repost, edited subject by mistake... > > On Monday, May 11, 2020, Tory M Blue wrote: >> >> And just to repeat. Same exact hardware, same kernel, nothing more than >> installing the latest postgres12, copying my config files from 9.5 to 12 >> an

Re: Is there a significant difference in Memory settings between 9.5 and 12

Repost, edited subject by mistake... On Monday, May 11, 2020, Tory M Blue wrote: > > And just to repeat. Same exact hardware, same kernel, nothing more than > installing the latest postgres12, copying my config files from 9.5 to 12 > and running the pg_upgrade. > You’ll want to remove the pg_upg

Re: Is there a significant difference in Memory settings between 9.5 and 12.

On Monday, May 11, 2020, Tory M Blue wrote: > > > And just to repeat. Same exact hardware, same kernel, nothing more than > installing the latest postgres12, copying my config files from 9.5 to 12 > and running the pg_upgrade. > You’ll want to remove the pg_upgrade from the equation and try v12

Re: Is there a significant difference in Memory settings between 9.5 and 12

On Mon, May 11, 2020 at 9:01 PM Thomas Munro wrote: > On Tue, May 12, 2020 at 2:52 PM Tory M Blue wrote: > > It took the change but didn't help. So 10GB of shared_buffers in 12 is > still a no go. I'm down to 5GB and it works, but this is the same hardware, > the same exact 9.5 configuration. So

Re: Is there a significant difference in Memory settings between 9.5 and 12

On Mon, May 11, 2020 at 9:57 PM Tom Lane wrote: > Tory M Blue writes: > > That may be the next step in the lab, but was hoping someone knew of a > > significant difference. > > I think we've made it perfectly clear that we don't. There's something > odd about your situation. > >

Re: PostgreSQL client hangs sometimes on 'EXEC SQL PREPARE sid_sisisinst FROM :select_anw;'

El día Montag, Mai 11, 2020 a las 02:41:29 -0400, Tom Lane escribió: > Matthias Apitz writes: > > Below is the exchange. The visible strings are: 'sisis' (the PG user), > > 'sisis123' (its password in PG, a dummy password used for testing) and > > 'srap32dxr1' the hostname. The server is terminat

Re: Is there a significant difference in Memory settings between 9.5 and 12

Tory M Blue writes: > That may be the next step in the lab, but was hoping someone knew of a > significant difference. I think we've made it perfectly clear that we don't. There's something odd about your situation. regards, tom lane

Re: Is there a significant difference in Memory settings between 9.5 and 12

On Mon, May 11, 2020 at 7:57 PM David G. Johnston < david.g.johns...@gmail.com> wrote: > On Monday, May 11, 2020, Tory M Blue wrote: > >> I'll keep digging, but i'm hesitant to do my multiple TB db's with half of their shared buffer configs, until I understand what 12 is doing different

Re: Is there a significant difference in Memory settings between 9.5 and 12

On Tue, May 12, 2020 at 2:52 PM Tory M Blue wrote: > It took the change but didn't help. So 10GB of shared_buffers in 12 is still > a no go. I'm down to 5GB and it works, but this is the same hardware, the > same exact 9.5 configuration. So I'm missing something. WE have not had to > mess with

Re: Firing an event trigger for ADD COLUMN?

On Mon, May 11, 2020 at 5:23 PM Alvaro Herrera wrote: > On 2020-May-11, Michel Pelletier wrote: > > > Is this asymmetry a bug? I realize these event trigger functions are > > typically written in C, but I'd like to keep this idea contained to a > > plpgsql function and cover as many reversible c

Re: Is there a significant difference in Memory settings between 9.5 and 12

On Monday, May 11, 2020, Tory M Blue wrote: > I'll keep digging, but i'm hesitant to do my multiple TB db's with half of >>> their shared buffer configs, until I understand what 12 is doing >>> differently than 9.5 >> >> Maybe run your test suite on 9.6, 10, and 11 to see if it is indeed new to 1

Re: Is there a significant difference in Memory settings between 9.5 and 12

On Mon, May 11, 2020 at 2:08 PM Tory M Blue wrote: > > > On Mon, May 11, 2020 at 1:36 PM Tom Lane wrote: > >> Tory M Blue writes: >> > 12 will not start at 10GB, even though it's the same hardware, same >> config >> > file, same physical box, same everything, just version 12 vs 9.5 >> >> For me

Re: Upgrade Process Says "The database server was not shut down cleanly" but it was

On 5/11/20 2:45 PM, TalGloz wrote: Well I tried your steps, both Postgres 10 and 12 are in perfect running conditions and were shut down using the "systemctl" command. When I execute: /usr/pgsql-12/bin/pg_upgrade --old-bindir=/usr/pgsql-10/bin/ --new-bindir=/usr/pgsql-12/bin/ --old-datadir=/var/

Re: Enforcing uniqueness on [real estate/postal] addresses

Peter Devoy writes: > Hi list > > I need to store addresses for properties (as in real estate) so in my > naivety I created a unique constraint like this: > > ALTER TABLE properties > ADD CONSTRAINT is_unique_address > UNIQUE ( > description, --e.g. Land north of Foo Cottage >

Re: Firing an event trigger for ADD COLUMN?

On 2020-May-11, Alvaro Herrera wrote: > https://www.postgresql.org/message-id/20150409161419.gc4...@alvh.no-ip.org (Please don't expect this code to compile anymore.) -- Álvaro Herrerahttps://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Servi

Re: Firing an event trigger for ADD COLUMN?

On 2020-May-11, Michel Pelletier wrote: > Is this asymmetry a bug? I realize these event trigger functions are > typically written in C, but I'd like to keep this idea contained to a > plpgsql function and cover as many reversible changes as I can. Sadly, you cannot obtain all the necessary deta

Re: Firing an event trigger for ADD COLUMN?

> Is this asymmetry a bug? I realize these event trigger functions are typically written in C, but I'd like to keep this idea contained to a plpgsql function and cover as many reversible changes as I can. This is the expected behavior since pg_event_trigger_ddl_commands function does not return o

Firing an event trigger for ADD COLUMN?

I'm writing a script that "reverses" ddl operations in an "up" script by capturing event triggers and generating a "down" script for reverting. I've got it working great for tables, indexes, etc, but it seems ADD COLUMN doesn't sent an event, here's the code i'm using, pasted straight from the docu

Re: Upgrade Process Says "The database server was not shut down cleanly" but it was

On 5/11/20 2:45 PM, TalGloz wrote: Well I tried your steps, both Postgres 10 and 12 are in perfect running conditions and were shut down using the "systemctl" command. When I execute: /usr/pgsql-12/bin/pg_upgrade --old-bindir=/usr/pgsql-10/bin/ --new-bindir=/usr/pgsql-12/bin/ --old-datadir=/var/

Re: Upgrade Process Says "The database server was not shut down cleanly" but it was

Well I tried your steps, both Postgres 10 and 12 are in perfect running conditions and were shut down using the "systemctl" command. When I execute: /usr/pgsql-12/bin/pg_upgrade --old-bindir=/usr/pgsql-10/bin/ --new-bindir=/usr/pgsql-12/bin/ --old-datadir=/var/lib/pgsql/10/data --new-datadir=/var

Re: Is there a significant difference in Memory settings between 9.5 and 12

Tory M Blue writes: > Okay the one difference I see in settings is this little gem in 12.. > shared_memory_type mmap Well, v12 is just exposing a switch for something that was hard-wired before. But now I wonder if your 9.5 installation could've been compiled to force it to use SysV shmem instea

Re: Is there a significant difference in Memory settings between 9.5 and 12

On 5/11/20 1:42 PM, Tory M Blue wrote: On Mon, May 11, 2020 at 1:36 PM Tom Lane > wrote: Tory M Blue mailto:tmb...@gmail.com>> writes: > 12 will not start at 10GB, even though it's the same hardware, same config > file, same physical box, same ever

Re: Hash partitioning, what function is used to compute the hash?

On 2020-May-11, Michael Lewis wrote: > Afaik, hash partition doesn't have real world expected use cases just yet. I don't think I agree with this assertion. While I understand that there might be things still to do in this area (as everywhere else), it should certainly have its uses already. If

Re: Is there a significant difference in Memory settings between 9.5 and 12

On Mon, May 11, 2020 at 1:36 PM Tom Lane wrote: > Tory M Blue writes: > > 12 will not start at 10GB, even though it's the same hardware, same > config > > file, same physical box, same everything, just version 12 vs 9.5 > > For me, using all-default settings (in particular, shared_buffers = > 12

Re: Is there a significant difference in Memory settings between 9.5 and 12

On Mon, May 11, 2020 at 1:36 PM Tom Lane wrote: > Tory M Blue writes: > > 12 will not start at 10GB, even though it's the same hardware, same > config > > file, same physical box, same everything, just version 12 vs 9.5 > > For me, using all-default settings (in particular, shared_buffers = > 12

Re: Is there a significant difference in Memory settings between 9.5 and 12

Tory M Blue writes: > 12 will not start at 10GB, even though it's the same hardware, same config > file, same physical box, same everything, just version 12 vs 9.5 For me, using all-default settings (in particular, shared_buffers = 128MB), the shared memory block is about 141.6MB using 9.5 and 14

Is there a significant difference in Memory settings between 9.5 and 12

Upgraded from 9.5 to 12 and 12 would not start with the current configured Shared Buffers. Same hardware, same config file. Which tells me something has changed, are there new default settings in the 12 postgresql.conf file that are not being called out in my 9.5 config file that could be the cau

Re: PostgreSQL client hangs sometimes on 'EXEC SQL PREPARE sid_sisisinst FROM :select_anw;'

El día Montag, Mai 11, 2020 a las 02:41:29 -0400, Tom Lane escribió: > Matthias Apitz writes: > > Below is the exchange. The visible strings are: 'sisis' (the PG user), > > 'sisis123' (its password in PG, a dummy password used for testing) and > > 'srap32dxr1' the hostname. The server is terminat

Re: Enforcing uniqueness on [real estate/postal] addresses

Hello everyone Thank you all for your suggestions. I had neither heard of partial unique indexes nor considered using COALESCE so I will explore both of these as options and update the thread with how it goes. >Then, if you can define a problem where you feel having a unique table >constraint o

Re: PostgreSQL client hangs sometimes on 'EXEC SQL PREPARE sid_sisisinst FROM :select_anw;'

Matthias Apitz writes: > Below is the exchange. The visible strings are: 'sisis' (the PG user), > 'sisis123' (its password in PG, a dummy password used for testing) and > 'srap32dxr1' the hostname. The server is terminating the connection with > a F-pkg: [ squint... ] That looks nothing like a P

Re: Enforcing uniqueness on [real estate/postal] addresses

On 5/11/20 9:55 AM, Peter Devoy wrote: Of course, if any of the fields are NULL (which they often are) I end up with duplicates. One solution may be to add NOT NULL constraints and use empty strings instead of NULL values but, until asking around today, I thought this was generally considered ba

Re: Enforcing uniqueness on [real estate/postal] addresses

On Mon, May 11, 2020 at 9:56 AM Peter Devoy wrote: > I need to store addresses for properties (as in real estate) so in my > naivety I created a unique constraint like this: > > ALTER TABLE properties > ADD CONSTRAINT is_unique_address > UNIQUE ( > description, --e.g. Land north o

Re: Enforcing uniqueness on [real estate/postal] addresses

On 5/11/20 9:55 AM, Peter Devoy wrote: Hi list I need to store addresses for properties (as in real estate) so in my naivety I created a unique constraint like this: ALTER TABLE properties ADD CONSTRAINT is_unique_address UNIQUE ( description, --e.g. Land north of Foo Cottage

Re: PostgreSQL client hangs sometimes on 'EXEC SQL PREPARE sid_sisisinst FROM :select_anw;'

El día Montag, Mai 11, 2020 a las 06:24:52 +0200, Matthias Apitz escribió: > Ah, yes. As this is just a dev host only, I set 'chmod 4755 /usr/bin/lsof' > and now I see, the connecting client is one of our Java tools and I can > look at the problem from this side. > With the knowledge which Java

Re: Enforcing uniqueness on [real estate/postal] addresses

> On May 11, 2020, at 12:55 PM, Peter Devoy wrote: > > Hi list > > I need to store addresses for properties (as in real estate) so in my > naivety I created a unique constraint like this: > > ALTER TABLE properties >ADD CONSTRAINT is_unique_address >UNIQUE ( >description, --e

Enforcing uniqueness on [real estate/postal] addresses

Hi list I need to store addresses for properties (as in real estate) so in my naivety I created a unique constraint like this: ALTER TABLE properties ADD CONSTRAINT is_unique_address UNIQUE ( description, --e.g. Land north of Foo Cottage address_identifier_general,

Re: Hash partitioning, what function is used to compute the hash?

On Mon, 2020-05-11 at 04:33 +, Dennis Ryan wrote: > Regarding hash partitioning, what is the function/algorithm that is used to > compute the hash for the partition key? I need to write a query like > > “SELECT unknown_partition_hash_function(id) AS hash_value, COUNT(id) AS > number_of_reco

Re: PostgreSQL client hangs sometimes on 'EXEC SQL PREPARE sid_sisisinst FROM :select_anw;'

El día Montag, Mai 11, 2020 a las 09:50:12 -0400, Tom Lane escribió: > > > postmaste 14622 postgres9u IPv4 131903453 0t0 TCP > > srap32dxr1.dev.oclc.org:5432->srap32dxr1.dev.oclc.org:44836 (ESTABLISHED) > > OK, so it's gotten a TCP not Unix-socket connection; that's already

Re: PostgreSQL client hangs sometimes on 'EXEC SQL PREPARE sid_sisisinst FROM :select_anw;'

Matthias Apitz writes: > I launched a shell script to get some more information in this case: ok > The process with the PID in question is 'postmaster': Yeah, this should actually be a newly-forked postmaster child process, but it won't have done anything yet to change its ps-visible command li

Re: Hash partitioning, what function is used to compute the hash?

The documentation shows it is just a modulus operation. If you partition on object_key % 3 then you will create three partitions for remainder values 0-2 for instance. Afaik, hash partition doesn't have real world expected use cases just yet. List or range is probably what you want to use.

Hash partitioning, what function is used to compute the hash?

Regarding hash partitioning, what is the function/algorithm that is used to compute the hash for the partition key? I need to write a query like “SELECT unknown_partition_hash_function(id) AS hash_value, COUNT(id) AS number_of_records FROM existing_table GROUP BY 1” Sent from Mail