Re: How to fetch data from tables in PostgreSQL

On Wed, Apr 18, 2018 at 9:48 AM, Adrian Klaver wrote: > On 04/18/2018 12:38 AM, vaibhav zaveri wrote: > >> Hi, >> >> Thanks for your reply >> Which are the important tables from which we need to get data? >> > > Important for what? > >> >> Regards, >> Vaibhav Zaveri >>

pg_upgrade: when the --old-bindir requires "additional" $libdir/ plugins?

Hi all, with a huge delay, I've seen this question [1]. . and it seems like the hstore.so was somewhat intimately integrated into OP's database so the '/usr/bin/pg_dump --schema-only --binary-upgrade --format=custom' called through 'pg_upgrade' failed with: pg_dump: [archiver (db)] query

Re: pg_upgrade: when the --old-bindir requires "additional" $libdir/ plugins?

On 04/18/2018 07:07 AM, Pavel Raiskup wrote: Hi all, with a huge delay, I've seen this question [1]. . and it seems like the hstore.so was somewhat intimately integrated into OP's database so the '/usr/bin/pg_dump --schema-only --binary-upgrade --format=custom' called through 'pg_upgrade'

Re: pg_upgrade: when the --old-bindir requires "additional" $libdir/ plugins?

On Wednesday, April 18, 2018 4:22:23 PM CEST Tom Lane wrote: > Pavel Raiskup writes: > > [2] https://bugzilla.redhat.com/show_bug.cgi?id=1557490 > > There are certainly plenty of reasons why extension .so's might be needed > during pg_dump, even in a binary-upgrade situation.

Re: pg_upgrade: when the --old-bindir requires "additional" $libdir/ plugins?

On 04/18/2018 07:22 AM, Tom Lane wrote: Pavel Raiskup writes: . and it seems like the hstore.so was somewhat intimately integrated into OP's database so the '/usr/bin/pg_dump --schema-only --binary-upgrade --format=custom' called through 'pg_upgrade' failed with:

Re: pg_upgrade: when the --old-bindir requires "additional" $libdir/ plugins?

Pavel Raiskup writes: > . and it seems like the hstore.so was somewhat intimately integrated into > OP's database so the '/usr/bin/pg_dump --schema-only --binary-upgrade > --format=custom' called through 'pg_upgrade' failed with: > pg_dump: [archiver (db)] query failed:

Re: pg_upgrade: when the --old-bindir requires "additional" $libdir/ plugins?

On Wednesday, April 18, 2018 4:43:01 PM CEST Adrian Klaver wrote: > On 04/18/2018 07:22 AM, Tom Lane wrote: > > Pavel Raiskup writes: > >> . and it seems like the hstore.so was somewhat intimately integrated into > >> OP's database so the '/usr/bin/pg_dump --schema-only

Re: pg_upgrade: when the --old-bindir requires "additional" $libdir/ plugins?

On 04/18/2018 08:04 AM, Pavel Raiskup wrote: On Wednesday, April 18, 2018 4:43:01 PM CEST Adrian Klaver wrote: I am obviously missing something. If the old server was using hstore in a database how could hstore.so could be accessible to it but not pg_dump? Because on Fedora we usually run

Re: pg_upgrade help

Hi Fabio, sorry to bother you again, its still failing with stopping both server (8.4 and 9.4) -bash-4.2$ /opt/rh/rh-postgresql94/root/usr/bin/pg_upgrade --old-bindir=/usr/bin --new-bindir=/opt/rh/rh-postgresql94/root/usr/bin --old-datadir=$OLDCLUSTER --new-datadir=$NEWCLUSTER connection to

Re: pg_locks.relation question

On Wed, Apr 18, 2018 at 5:35 PM, Daniel Westermann < daniel.westerm...@dbi-services.com> wrote: > Hi, > > session one: > > locks=# begin; > BEGIN > locks=# alter table test add column b text; > ALTER TABLE > locks=# > > Session 2 querying pg_locks for the PID from above: > > locks=# select

Re: pg_upgrade help

did you run initdb on the new db? what happens if you manually start the new db? /opt/rh/rh-postgresql94/root/usr/bin/pg_ctl start -o "-p 50432 -c listen_addresses='' -c unix_socket_permissions=0700" -D $NEWCLUSTER after starting it, can you connect to it using psql? psql -p 50432 -h

Re: How to fetch data from tables in PostgreSQL

On 04/18/2018 12:38 AM, vaibhav zaveri wrote: Hi, Thanks for your reply Which are the important tables from which we need to get data? Important for what? Regards, Vaibhav Zaveri On 18 Apr 2018 12:52, "vaibhav zaveri" > wrote:

Re: pg_locks.relation question

On Wed, Apr 18, 2018 at 5:35 PM, Daniel Westermann < [ mailto:daniel.westerm...@dbi-services.com | daniel.westerm...@dbi-services.com ] > wrote: Hi, session one: locks=# begin; BEGIN locks=# alter table test add column b text; ALTER TABLE locks=# Session 2 querying pg_locks for the

Re: Can PostgreSQL create new WAL files instead of reusing old ones?

On Wed, Apr 18, 2018 at 12:35 AM, Michael Paquier wrote: > > That looks like a rather difficult problem to solve in PostgreSQL > itself, as the operator running the cluster is in charge of setting up > the FS options which would control the COW behavior, so it seems to me >

Tricking the optimizer

Hi, In order to implement some security features in our application, we sometimes append additional WHERE filters to our queries, so the filer in the end looks like: SELECT ... FROM ... WHERE securityFilter1 AND securityFilter2 AND securityFilter3 In the EXPLAIN the filters look something

Re: pg_upgrade help

On 04/18/2018 05:02 AM, Akshay Ballarpure wrote: Hi Fabio, sorry to bother you again, its still failing with stopping both server (8.4 and 9.4) Actually according to the command show at bottom of post it is failing trying to start the 8.4 server. In your previous post that was because it

pg_locks.relation question

Hi, session one: locks=# begin; BEGIN locks=# alter table test add column b text; ALTER TABLE locks=# Session 2 querying pg_locks for the PID from above: locks=# select locktype,relation::regclass,mode from pg_locks where pid = 2026 and locktype = 'relation'; locktype | relation |

Re: Tricking the optimizer

Vitaliy Garnashevich writes: > Is there any trick to craft the query in such a way, so that to make the > optimizer believe that the filters would not remove any rows, and all > rows will likely be returned by the query? If you don't mind writing some C code, you could

postgres on physical replica crashes

Hello. I've had several instances where postgres on my physical replica under version 9.6.6 is crashing with messages like the following in the logs: 2018-04-18 05:43:26 UTC dbname 5acf5e4a.6918 dbuser DETAIL: The postmaster has commanded this server process to roll back the current transaction

Re: Tricking the optimizer

We'd rather avoid writing C code. We're trying to stick with the DB functionality provided by default. Could something like the following, in theory, make things at least somewhat better in our case (without using the selectivity override)? ... WHERE coalesce(securityFilter1 AND

Re: Tricking the optimizer

We've tried to use "... WHERE coalesce(...)::bool;" on a test query: Before: ->  Seq Scan on public.sys_user sys_user_1  (cost=0.00..27140.46 rows=1 width=16) (actual time=0.105..38.627 rows=3289 loops=1)       Filter: (((SubPlan 7) = 0) AND ((SubPlan 8) = 0) AND ((SubPlan 9) = 0)) After: -> 

New website

I would contact the Webmaster but Contact goes to a big image of an elephant head. That is also where Downloads, Support and Donate lands. Might have been a good idea to roll out a demo site for testing first. Will reserve judgment on the site design until it is functioning. -- Adrian Klaver

pg_upgrade help

Hi all, I need help on pg_upgrade from 8.4 to 9.4 version. Appreciate urgent response. Installed both version and stopped it. Do i need to run both version or only one 8.4 or 9.4 . Both should run on 50432 ? -bash-4.2$ id uid=26(postgres) gid=26(postgres) groups=26(postgres)

Re: pg_upgrade help

Thanks Fabio for instant reply. I now started 8.4 with 50432 and 9.4 with default port but still its failing ...Can you please suggest what is wrong ? -bash-4.2$ /opt/rh/rh-postgresql94/root/usr/bin/pg_upgrade --old-bindir=/usr/bin --new-bindir=/opt/rh/rh-postgresql94/root/usr/bin

Re: pg_upgrade help

Hi, please avoid crossposting to multiple mailing lists. You need to run both versions of the database, the old and the new. They need to run on different ports (note that it is impossible to run 2 different processes on the same port, that's not a postgresql thing) On 04/18/2018 09:30 AM,

Re: rehashing catalog cache id 14 for pg_opclass; 17 tups, 8 buckets

Thiemo Kellner, NHC Barhufpflege wrote: > > What do you have your log levels set to? > > Thanks for pointing this out. I put client level to debug1. So, I am > just lucky not to have got flooded with Messages? Sort of. This is a normal operation and should not worry you. Yours, Laurenz Albe

Re: How to fetch data from tables in PostgreSQL

Hi, Thanks for your reply Which are the important tables from which we need to get data? Regards, Vaibhav Zaveri On 18 Apr 2018 12:52, "vaibhav zaveri" wrote: > Hi, > > How to fetch data from tables in PostgreSQL. > > Regards, > Vaibhav Zaveri >

Re: rehashing catalog cache id 14 for pg_opclass; 17 tups, 8 buckets

Zitat von Adrian Klaver : What do you have your log levels set to? Thanks for pointing this out. I put client level to debug1. So, I am just lucky not to have got flooded with Messages? -- Öffentlicher PGP-Schlüssel:

Re: How to fetch data from tables in PostgreSQL

vaibhav zaveri schrieb am 18.04.2018 um 09:22: > How to fetch data from tables in PostgreSQL. That is done using the SELECT statement: https://www.postgresql.org/docs/current/static/sql-select.html For an introduction on how to write queries, you might want to have a look at the Postgres

Re: How to fetch data from tables in PostgreSQL

Hi Vaibhav, I think this might come of help: https://www.postgresql.org/docs/current/static/tutorial-select.html On 04/18/2018 09:22 AM, vaibhav zaveri wrote: > Hi,  > > How to fetch data from tables in PostgreSQL. > > Regards,  > Vaibhav Zaveri

Re: pg_upgrade help

Hi Both version should be correctly stopped. pg_upgrade started clusters itself. Please check pg_upgrade_server.log file in directory where pg_upgrade was run. Also where is postgresql.conf? In PGDATA? Otherwise you need tell pg_upgrade correct path, for example with options '-o " -c

How to fetch data from tables in PostgreSQL

Hi, How to fetch data from tables in PostgreSQL. Regards, Vaibhav Zaveri

Re: How to fetch data from tables in PostgreSQL

SELECT * FROM table_name LIMIT 10; should give 10 records from your data table. On Wed, 18 Apr 2018, 17:22 vaibhav zaveri, wrote: > Hi, > > How to fetch data from tables in PostgreSQL. > > Regards, > Vaibhav Zaveri >

Re: pg_upgrade help

Hi, i was too fast in reply (and perhaps i should drink my morning coffee before replying), I will try to be more detailed: both servers should be able to run at the moment you run pg_upgrade, that means the 2 servers should have been correctly stopped in advance, should have their configuration

Re: Inconsistent compilation error

On 04/18/2018 06:02 PM, r...@raf.org wrote: Hi, But I'm getting this compilation error when it tries to load this function: ERROR: too many parameters specified for RAISE CONTEXT: compilation of PL/pgSQL function "tla_audit_delete_thing" near line 9 Traceback (most

Re: New website

On 04/18/2018 03:07 PM, Peter Geoghegan wrote: On Wed, Apr 18, 2018 at 3:03 PM, Adrian Klaver wrote: I would contact the Webmaster but Contact goes to a big image of an elephant head. That is also where Downloads, Support and Donate lands. Might have been a good idea

Re: New website

On Wed, Apr 18, 2018 at 5:03 PM, Adrian Klaver wrote: > I would contact the Webmaster but Contact goes to a big image of an > elephant head. That is also where Downloads, Support and Donate lands. > Might have been a good idea to roll out a demo site for testing first.

Re: New website

On Wed, Apr 18, 2018 at 3:03 PM, Adrian Klaver wrote: > I would contact the Webmaster but Contact goes to a big image of an > elephant head. That is also where Downloads, Support and Donate lands. > Might have been a good idea to roll out a demo site for testing first.

Re: New website

On 04/18/2018 03:06 PM, David G. Johnston wrote: On Wed, Apr 18, 2018 at 3:03 PM, Adrian Klaver >wrote: I would contact the Webmaster but Contact goes to a big image of an elephant head. That is also where Downloads, Support

Re: pg_upgrade help

Hello Akshay, Try starting both servers individually. If you can then,it may be port conflict Below is the part of document- Obviously, no one should be accessing the clusters during the upgrade. pg_upgrade defaults to running servers on port 50432 to avoid unintended client connections. You

Re: New website

On Wed, Apr 18, 2018 at 3:03 PM, Adrian Klaver wrote: > I would contact the Webmaster but Contact goes to a big image of an elephant > head. That is also where Downloads, Support and Donate lands. Might have > been a good idea to roll out a demo site for testing first.

Re: New website

On 04/18/2018 03:07 PM, Peter Geoghegan wrote: On Wed, Apr 18, 2018 at 3:03 PM, Adrian Klaver wrote: I would contact the Webmaster but Contact goes to a big image of an elephant head. That is also where Downloads, Support and Donate lands. Might have been a good idea

Re: New website

> On Apr 18, 2018, at 6:40 PM, Adrian Klaver wrote: > >> On 04/18/2018 03:07 PM, Peter Geoghegan wrote: >> On Wed, Apr 18, 2018 at 3:03 PM, Adrian Klaver >> wrote: >>> I would contact the Webmaster but Contact goes to a big image of an

Re: Inconsistent compilation error

On 04/18/2018 06:02 PM, r...@raf.org wrote: Hi, postgresql-9.5.12 on debian-9 I have a stored function with code that looks like: create or replace function tla_audit_delete_thing() returns boolean stable language plpgsql as $$ declare r record; status boolean

Re: New website

On 04/18/2018 05:22 PM, Jonathan S. Katz wrote: On Apr 18, 2018, at 6:40 PM, Adrian Klaver wrote: On 04/18/2018 03:07 PM, Peter Geoghegan wrote: On Wed, Apr 18, 2018 at 3:03 PM, Adrian Klaver wrote: I would contact the Webmaster but

Re: New website

On 04/18/2018 05:31 PM, Peter Geoghegan wrote: On Wed, Apr 18, 2018 at 3:40 PM, Adrian Klaver wrote: And to get to the mailing list archives(arguably one of the most important links) you have to: Click on Community, click on Mailing Lists on the left sidebar, then

Inconsistent compilation error

Hi, postgresql-9.5.12 on debian-9 I have a stored function with code that looks like: create or replace function tla_audit_delete_thing() returns boolean stable language plpgsql as $$ declare r record; status boolean := 1; begin for r in select _.* from

Re: Inconsistent compilation error

On Wed, Apr 18, 2018 at 9:02 PM, wrote: > Hi, > > postgresql-9.5.12 on debian-9 > > I have a stored function with code that looks like: > > create or replace function tla_audit_delete_thing() > returns boolean stable language plpgsql as $$ > declare > r record;

Re: New website

On Wed, Apr 18, 2018 at 3:40 PM, Adrian Klaver wrote: > And to get to the mailing list archives(arguably one of the most important > links) you have to: > > Click on Community, click on Mailing Lists on the left sidebar, then scroll > to the bottom of page to find the

Re: Inconsistent compilation error

On 19 April 2018 at 13:28, Melvin Davidson wrote: > It is not fine. You have specifed TWO percent signs (%) which requires TWO > argumenrts, > but you have only provided ONE -> r. I'm confused about this statement. Did you perhaps overlook the fact that there are in fact

Re: Inconsistent compilation error

On Wed, Apr 18, 2018 at 7:04 PM, Melvin Davidson wrote: > > Normally, literals are inside the first quotes. IE: raise notice ' > blah_history.original_id' %', r; > ​But a compiler doesn't care about human concepts like "normally" - it just cares about "syntactically