Re: How can I retrieve double or int data type for libpq

a wrote: > I wanna use C library to connect pgsql server and I got 2 questions: This is all well documented in https://www.postgresql.org/docs/current/static/libpq.html > 1, If I can fetch a row's data instead of using PQgetvalue? PGgetvalue *is* the way to fetch the row's data. There is no

Re: Logging

Thanks All, It seems that the related DETAIL message appears if the query is successful. On error the DETAIL log line is missing... This makes debugging difficult, considering the insert is coming from Dreamfactory via a rest POST. I am finding it very difficult navigating my way through the

Re: Logging

On 06/12/2018 08:25 PM, David G. Johnston wrote: On Tuesday, June 12, 2018, Andrew Bartley > wrote: On Wed, 13 Jun 2018 at 12:43 Laurenz Albe mailto:laurenz.a...@cybertec.at>> wrote: log_min_duration_statement = 0 [...]

Re: Logging

Ok, thanks. will try On Wed, 13 Jun 2018 at 13:25 David G. Johnston wrote: > On Tuesday, June 12, 2018, Andrew Bartley wrote: > >> On Wed, 13 Jun 2018 at 12:43 Laurenz Albe >> wrote: >> > >>> log_min_duration_statement = 0 >>> >> [...] > >> >> log_min_duration_statement -1 >> > > You've

Re: Logging

On Tuesday, June 12, 2018, Andrew Bartley wrote: > > > On Wed, 13 Jun 2018 at 12:43 Laurenz Albe > wrote: > >> >> log_min_duration_statement = 0 >> > [...] > > log_min_duration_statement -1 > You've disabled statement logging altogether. The zero value you were directed to use is what causes

How can I retrieve double or int data type for libpq

Hi I wanna use C library to connect pgsql server and I got 2 questions: 1, If I can fetch a row's data instead of using PQgetvalue? 2, PQgetvalue will return all data as text, I would have to convert them into the relative data type, is there a way of getting data by its original format?

Re: Logging

On Wed, 13 Jun 2018 at 12:43 Laurenz Albe wrote: > Andrew Bartley wrote: > > Can someone please tell me how to log the values being inserted in this > example.. > > > > 2018-06-12 22:31:49 UTC-5b2049d4.434ac-STATEMENT: insert into > "api_consumers" ("consumer_id", "create_datetime") values ($1,

Re: Logging

Andrew Bartley wrote: > Can someone please tell me how to log the values being inserted in this > example.. > > 2018-06-12 22:31:49 UTC-5b2049d4.434ac-STATEMENT: insert into > "api_consumers" ("consumer_id", "create_datetime") values ($1, $2). > > I have tried many different logging options

Re: Does pgAgent support chinese, japanese characters?

Thanks a lot for your reply. I tried to set the host/client encoding, however, when I create new tables using Chinese, it reported an encoding error.. also with other queries. May I ask encoding of pgAgent? May it reading from a text file? Or I can use notepad++ to convert and then

Re: Print pg_lsn as a number?

Hi, On 2018-06-13 09:18:21 +0900, Michael Paquier wrote: > We map pg_wal_lsn_diff result to a numeric, so that could make sense to > use numeric as well here, which is not the greatest choice by the way as > that's an int64 internally, but that's more portable for any > (unlikely-to-happen)

Re: Print pg_lsn as a number?

On Tue, Jun 12, 2018 at 10:39:43AM -0700, Andres Freund wrote: > On 2018-06-12 09:31:49 -0700, Scott Stroupe wrote: >> According to the documentation[1], pg_lsn is a 64-bit integer that's >> printed as two hex numbers separated by a slash, e.g. 68/1225BB70. Is >> there a way to get the 64-bit

Re: pg_upgrade 10.2

Murthy Nunna writes: BTW, this message was and remained cross-posted to 3 groups which is considered bad style around here and I was negligent too in the previous reply which also went out to all of them. Please take note. Thank -- Jerry Sievers Postgres DBA/Development Consulting e:

Re: pg_upgrade 10.2

Murthy Nunna writes: > Jerry, > > OMG, I think you nailed this... I know what I did. I cut/pasted the > command from an e-mail... I have seen this issue before with stuff not Oh! I suggest you lose that habit ASAP before ever issuing another command to anything :-) > related to postgres. But

Re: pg_upgrade 10.2

On 06/12/2018 02:49 PM, Murthy Nunna wrote: Hi Adrian, Port numbers are correct. I moved the position of -c (-p 5433 -P 5434 -c -r -v). Now it is NOT complaining about old cluster running. However, I am running into a different problem. New cluster database "ifb_prd_last" is not empty

RE: pg_upgrade 10.2

Jerry, OMG, I think you nailed this... I know what I did. I cut/pasted the command from an e-mail... I have seen this issue before with stuff not related to postgres. But then those commands failed in syntax error and then you know what you did wrong. Similarly, I expect pg_upgrade to throw

Logging

Hi all, Can someone please tell me how to log the values being inserted in this example.. 2018-06-12 22:31:49 UTC-5b2049d4.434ac-STATEMENT: insert into "api_consumers" ("consumer_id", "create_datetime") values ($1, $2). I have tried many different logging options and combinations. Thanks

RE: pg_upgrade 10.2

Hi Adrian, Port numbers are correct. I moved the position of -c (-p 5433 -P 5434 -c -r -v). Now it is NOT complaining about old cluster running. However, I am running into a different problem. New cluster database "ifb_prd_last" is not empty Failure, exiting Note: ifb_prd_last is not new

Re: Out of memory error with PG10.3, 10.4 but not 9.3.19

Le mercredi 16 mai 2018 à 09:48:54 UTC+2, ChatPristi a écrit : Dear all, I have a SELECT command (in partitionned tables) that failed with:psql:/tmp/query.txt:1: ERROR:  out of memory DETAIL:  Cannot enlarge string buffer containing 1073741818 bytes by 32 more bytes. I got the error

Re: pg_upgrade 10.2

On 06/12/2018 02:18 PM, Murthy Nunna wrote: pg_upgrade -V pg_upgrade (PostgreSQL) 10.4 pg_upgrade -b /fnal/ups/prd/postgres/v9_3_14_x64/Linux-2-6/bin -B /fnal/ups/prd/postgres/v10_4_x64/Linux-2-6/bin -d /data0/pgdata/ifb_prd_last -D /data0/pgdata/ifb_prd_last_104 -p 5433 -P 5434 -r -v –c

RE: pg_upgrade 10.2

pg_upgrade -V pg_upgrade (PostgreSQL) 10.4 pg_upgrade -b /fnal/ups/prd/postgres/v9_3_14_x64/Linux-2-6/bin -B /fnal/ups/prd/postgres/v10_4_x64/Linux-2-6/bin -d /data0/pgdata/ifb_prd_last -D /data0/pgdata/ifb_prd_last_104 -p 5433 -P 5434 -r -v –c -Original Message- From: Adrian Klaver

Re: pg_upgrade 10.2

On 06/12/2018 01:58 PM, Murthy Nunna wrote: Thanks Adrian. I removed "-k" flag. But still got same error. There seems to be a postmaster servicing the old cluster. Please shutdown that postmaster and try again. Failure, exiting Well according to the code in pg_upgrade.c that message should

Re: pg_upgrade and wraparound

Back again, >> Alexander, could you hack things up so autovacuum logging is enabled >> (log_autovacuum_min_duration=0), and see whether it's triggered? I've changed this config setting in both 9.6 and 10.4 postgresql.conf, then I've ran pg_upgrade once more. However I'm not sure how can I see

RE: pg_upgrade 10.2

Thanks Adrian. I removed "-k" flag. But still got same error. There seems to be a postmaster servicing the old cluster. Please shutdown that postmaster and try again. Failure, exiting -Original Message- From: Adrian Klaver [mailto:adrian.kla...@aklaver.com] Sent: Tuesday, June 12, 2018

Re: pg_upgrade 10.2

On 06/12/2018 01:34 PM, Murthy Nunna wrote: In older versions of pg_upgrade (e.g from 9.2 to 9.3), I was able to run pg_upgrade without stopping old cluster using the check flag. pg_upgrade -b -B -d -D -p 5432 -P 5434 -r -v -k -c Note the “c” flag at the end I take the below to it

pg_upgrade 10.2

In older versions of pg_upgrade (e.g from 9.2 to 9.3), I was able to run pg_upgrade without stopping old cluster using the check flag. pg_upgrade -b -B -d -D -p 5432 -P 5434 -r -v -k -c Note the "c" flag at the end However pg_upgrade in 10 (I tried from 9.3 to 10.4), when I did not stop

Re: Print pg_lsn as a number?

> On Jun 12, 2018, at 10:39 AM, Andres Freund wrote: > > Hi, > > On 2018-06-12 09:31:49 -0700, Scott Stroupe wrote: >> According to the documentation[1], pg_lsn is a 64-bit integer that's >> printed as two hex numbers separated by a slash, e.g. 68/1225BB70. Is >> there a way to get the 64-bit

Re: What does Natvie Posgres mean?

This, to me, is the true meaning of "native PostgreSQL" (as opposed to "stock PostgreSQL", which is uncustomized code).  However, if the job wanted post was written by an HR flunky, it could mean anything. On 06/12/2018 01:11 PM, Benjamin Scherrey wrote: In my experience it refers to

Re: ERROR: found multixact from before relminmxid

Hi, On 2018-06-08 13:30:33 -0500, Jeremy Finzel wrote: >Avoid spuriously marking pages as all-visible (Dan Wood, Pavan Deolasee, >Álvaro Herrera) > >This could happen if some tuples were locked (but not deleted). While >queries would still function correctly, vacuum would

Re: Transparent partitioning

On 06/12/2018 11:12 AM, Bráulio Bhavamitra wrote: Hi postgresql developers, Are there any plans to have transparent partitioning through clustered indexes in such a way that a table is partitioned in the backend according to a clustering index without any further user configuration?

Re: PG on AWS RDS and IAM authentication

We are working on adding IAM authentication for both RDS for PostgreSQL and Aurora PostgreSQL, and expect to release support for both soon. Thanks, -- Kevin Jernigan Senior Product Manager Amazon Aurora PostgreSQL 1-415-710-8828 (m) k...@amazon.com 27 Melcher Street Boston, MA 02210 On

Transparent partitioning

Hi postgresql developers, Are there any plans to have transparent partitioning through clustered indexes in such a way that a table is partitioned in the backend according to a clustering index without any further user configuration? Best regards, Braulio Oliveira

Re: What does Natvie Posgres mean?

In my experience it refers to development directly via SQL against the Postgres server rather than via an ORM like Django or the like. A remarkably high percentage of applications backed by Postgres have been written by developers that have never actually seen or written SQL code directly. It's

Re: Print pg_lsn as a number?

On Tue, Jun 12, 2018 at 6:31 PM, Scott Stroupe wrote: > According to the documentation[1], pg_lsn is a 64-bit integer that's printed > as two hex numbers separated by a slash, e.g. 68/1225BB70. Is there a way to > get the 64-bit integer in a common numeric representation instead of the >

Re: What does Natvie Posgres mean?

On 06/12/2018 10:53 AM, bto...@computer.org wrote: When someone, e.g., as appeared in a recent and some older pgsql-jobs messages, says "Native Postgres", what do you suppose that means? I'm going to say it means the community supported versions, not offshoots or forks e.g. AWS RDS

What does Natvie Posgres mean?

When someone, e.g., as appeared in a recent and some older pgsql-jobs messages, says "Native Postgres", what do you suppose that means? Does it mean something different than just "PostgreSQL"? Is the word "Native" just noise, or does it actually refer to something specific? A quick google

Re: Print pg_lsn as a number?

Hi, On 2018-06-12 09:31:49 -0700, Scott Stroupe wrote: > According to the documentation[1], pg_lsn is a 64-bit integer that's > printed as two hex numbers separated by a slash, e.g. 68/1225BB70. Is > there a way to get the 64-bit integer in a common numeric > representation instead of the

Re: Software that can automatically make sense of a DB's tables and ID names

On 06/12/2018 10:14 AM, Philip Rhoades wrote: People, I haven't really been keeping up with what is happening in the PG world - now I have an old Discourse DB that I want to extract some of the categories and topics from to insert into the current setup.  What I was wondering is if anyone

Software that can automatically make sense of a DB's tables and ID names

People, I haven't really been keeping up with what is happening in the PG world - now I have an old Discourse DB that I want to extract some of the categories and topics from to insert into the current setup. What I was wondering is if anyone has developed software in recent years that can

Re: PG on AWS RDS and IAM authentication

On 06/12/2018 08:45 AM, Ravi Krishna wrote: As per https://forums.aws.amazon.com/thread.jspa?threadID=258822=0 there was no IAM authentication for PG on AWS RDS. (It is there for MySQL). However the link is a year old. Has it changed since then? Can we use IAM authentication for PG. IAM

Print pg_lsn as a number?

According to the documentation[1], pg_lsn is a 64-bit integer that's printed as two hex numbers separated by a slash, e.g. 68/1225BB70. Is there a way to get the 64-bit integer in a common numeric representation instead of the peculiar hex-slash-hex representation? Thanks, Scott [1]

Re: Bad performance with cascaded deletes

On Tue, Jun 12, 2018 at 10:48 AM, Don Seiler wrote: > On Tue, Jun 12, 2018 at 2:26 AM, Haug Bürger > wrote: > >> >> "availability_cache_ac_session_id_fkey" FOREIGN KEY (ac_session_id) >> REFERENCES zpg_data.session(id) ON DELETE CASCADE >> >> > Do you have an index on

Re: Bad performance with cascaded deletes

On Tue, Jun 12, 2018 at 2:26 AM, Haug Bürger wrote: > > "availability_cache_ac_session_id_fkey" FOREIGN KEY (ac_session_id) > REFERENCES zpg_data.session(id) ON DELETE CASCADE > > Do you have an index on availability_cache.ac_session_id? These fields are not automatically indexed and that

PG on AWS RDS and IAM authentication

As per https://forums.aws.amazon.com/thread.jspa?threadID=258822=0 there was no IAM authentication for PG on AWS RDS. (It is there for MySQL). However the link is a year old. Has it changed since then? Can we use IAM authentication for PG. Thanks

Re: Semantics around INSERT INTO with SELECT and ORDER BY.

On 06/12/2018 01:39 AM, Steve Krenzel wrote: This is relevant for tables that have a column with a SERIAL type, I need to guarantee that the relative ordering remains the same as the ordering of the selected result set. More concretely, given:     > CREATE TABLE foo (id SERIAL, val TEXT);  

Re: Semantics around INSERT INTO with SELECT and ORDER BY.

On Tuesday, June 12, 2018, Steve Krenzel wrote: > This is relevant for tables that have a column with a SERIAL type, I need > to guarantee that the relative ordering remains the same as the ordering of > the selected result set. > The logical insertion order, and thus the sequence values, will

Re: pg_upgrade and wraparound

Andres Freund wrote: > I'm not entirely clear why pg_restore appears to use a separate > transaction for each large object, surely exascerbating the problem. To make sure that per-object locks don't fill up the shared lock table? There might be hundreds of thousands of large objects. If

Re: Add to watchdog cluster request is rejected by node

Hi, I think it would be better to make questions in the Pgpool-II dedicated mailing list pgpool-gene...@pgpool.net. > 3240: WARNING: checking setuid bit of if_up_cmd > Jun 11 08:57:04 asa-pgpool02 pgpool[3240]: [1-2] 2018-06-11 08:57:04: pid > 3240: DETAIL: ifup[/sbin/ip] doesn't have setuid

Re: Semantics around INSERT INTO with SELECT and ORDER BY.

>Or to put it another way, I want to select values from one table ordered by >complex criteria and insert them into another table. I want to be able to >retrieve the rows from the target table in the same order they were inserted, >but I don't care about the specific ordering criteria. I only care

Re: Bad performance with cascaded deletes

Haug Bürger wrote: > Delete on zpg_data.session (cost=190.51..4491.20 rows=500 width=46) > ... > Planning time: 0.222 ms > Trigger RI_ConstraintTrigger_a_16481 for constraint > availability_cache_ac_session_id_fkey: time=350.116 calls=500 > > For me this reads like the delete takes 0.2ms and

Re: Semantics around INSERT INTO with SELECT and ORDER BY.

This is relevant for tables that have a column with a SERIAL type, I need to guarantee that the relative ordering remains the same as the ordering of the selected result set. More concretely, given: > CREATE TABLE foo (id SERIAL, val TEXT); > CREATE TABLE bar (id SERIAL, val TEXT); >

Re: Semantics around INSERT INTO with SELECT and ORDER BY.

Why is it even important? Once you use ORDER BY clause, you are guaranteed to get the rows in the order. Why do you need how it was inserted in the first place.

Semantics around INSERT INTO with SELECT and ORDER BY.

If I insert using the results of a select statement, are the inserts guaranteed to happen in the order of the rows returned from the select? That is, are these two equivalent: INSERT INTO SELECT FROM ORDER BY DESC; And: FOR row IN SELECT FROM ORDER BY DESC LOOP INSERT

Does pgAgent support chinese, japanese characters?

Hi I'm using pgAgent to set up daily or monthly tasks. I have tables that with the name of Chinese or Japanese. It runs ok in normal sql script, but report non-recognizable in pgAgent SQL jobs. I'm using windows server 2008, pgsql 10, and pgAgent 3.4, may there be a way of supporting

Bad performance with cascaded deletes

I have an issue with delete performance I can't explain. Delete on zpg_data.session (cost=190.51..4491.20 rows=500 width=46) ... Planning time: 0.222 ms Trigger RI_ConstraintTrigger_a_16481 for constraint availability_cache_ac_session_id_fkey: time=350.116 calls=500 For me this reads like the