Re: [GENERAL] Confusing with commit time usage in logical decoding

Hi, On 2016-02-29 11:12:14 +0100, Weiping Qu wrote: > If you received this message twice, sorry for annoying since I did not > subscribe successfully previously due to conflicting email domain. > > Dear postgresql general mailing list, > > I am currently using the logical decoding feature

Re: [GENERAL] bloated postgres data folder, clean up

Hi, So, I still think the SQL function isnt really working well. Here’s what I did: /data/postgres # psql postgres=# \copy ( select * from find_useless_postgres_file('live') ) to /tmp/useless_files.csv delimiter ';' csv header; postgres=# \q /data/postgres # wc -l /tmp/useless_files.csv 7422

Re: [GENERAL] CStringGetTextDatum and other conversions in server-side code

On 3/2/2016 9:36 PM, da...@andl.org wrote: [dmb>] This is fairly easy for ints and reals, but is particularly a problem for all the variable length types (eg text, time and decimal). all the text types are simply a 32bit length and an array of characters. you need to be aware of the

Re: [GENERAL] CStringGetTextDatum and other conversions in server-side code

> [dmb>] So how would I go about finding a set of useful conversion functions > for basic types (real, decimal, time, etc)? the basic SQL to C mappings are defined by the H files listed here, http://www.postgresql.org/docs/current/static/xfunc-c.html#XFUNC-C-TYPE-TABLE [dmb>] [dmb>] Yes,

Re: [GENERAL] Re: could not migrate 8.0.13 database with large object data to 9.5.1

On 3/2/2016 5:52 PM, Premsun Choltanwanich wrote: And, almost tables are transferred to new server except tables which contain lo data (all those tables are missing from the database) after running pg_dump and psql following as per your suggestion. The attachment is a log file created after

Re: [GENERAL] CStringGetTextDatum and other conversions in server-side code

On 03/02/2016 04:20 PM, da...@andl.org wrote: > (please do not post HTML to these lists; see: > https://wiki.postgresql.org/wiki/Mailing_Lists) > [dmb>] I checked the list first: it looks like about 25-50% HTML. > Happy to oblige, but I think you've got your work cut out. Understood, but you

[GENERAL] Re: could not migrate 8.0.13 database with large object data to 9.5.1

Hi Magnus, My database size is about 1.5 GB by most of them are lo (large object) data. Regards, NETsolutions Asia Limited http://www.nsasia.co.th >>> Magnus Hagander 2016-03-02 15:29 >>> On Mar 2, 2016 06:01, "John R Pierce" wrote: > > (thread

Re: [GENERAL] Export binary data - PostgreSQL 9.2

Hi, You can use pg_dump with -t and -Fc option to take dump of a table in compressed format. $PGBIN/pg_dump -t -Fc -d -f /tmp/table.dmp For more information you can refer below link: http://www.postgresql.org/docs/9.2/static/app-pgdump.html Hope this would help. On Thu, Mar 3, 2016 at

Re: [GENERAL] CStringGetTextDatum and other conversions in server-side code

On 3/2/2016 4:20 PM, da...@andl.org wrote: [dmb>] So how would I go about finding a set of useful conversion functions for basic types (real, decimal, time, etc)? the basic SQL to C mappings are defined by the H files listed here,

Re: [GENERAL] CStringGetTextDatum and other conversions in server-side code

(please do not post HTML to these lists; see: https://wiki.postgresql.org/wiki/Mailing_Lists) [dmb>] I checked the list first: it looks like about 25-50% HTML. Happy to oblige, but I think you've got your work cut out. > and looks like it might work. Questions: > > 1. Is this the right

[GENERAL] BDR concern/issue

I queried pg_replication_slots after I removed an BDR node and I noticed a slot_name that isn't in bdr.bdr_node_slots. And active is 'f' and it has been retaining bytes. Should I be concerned and is there a way to remove it. I do still have one UDR node which is running

Re: [GENERAL] CStringGetTextDatum and other conversions in server-side code

(please do not post HTML to these lists; see: https://wiki.postgresql.org/wiki/Mailing_Lists) On 03/02/2016 03:06 PM, da...@andl.org wrote: > Writing a language handler: pl_language_handler. Need to do a variety of > data conversions. One of them is char* C-string to and from Text/Varchar. > >

Re: [GENERAL] Issue enabling track_counts to launch autovacuum in 9.4.5

On Wed, Mar 2, 2016 at 4:25 PM, Tom Lane wrote: > "David G. Johnston" writes: > > ​The fact that the first two are only LOG level and not WARNING would > seems > > like the easiest improvement to make. > > Unfortunately, that would be a

Re: [GENERAL] Issue enabling track_counts to launch autovacuum in 9.4.5

"David G. Johnston" writes: > ​The fact that the first two are only LOG level and not WARNING would seems > like the easiest improvement to make. Unfortunately, that would be a disimprovement, because in many common configurations WARNING messages don't appear in

Re: [GENERAL] Issue enabling track_counts to launch autovacuum in 9.4.5

On 03/02/2016 02:49 PM, Tom Lane wrote: Or maybe the problem was that when we forced track_counts off because of no stats collector, we didn't emit any bleat noting that, which if we had might have led you to realize that the above messages were the direct cause of the next one: 2016-03-02

Re: [GENERAL] Postgresql upgrade 9.5

Upgrade from 9.4.5 On Wed, Mar 2, 2016 at 3:09 PM, David G. Johnston < david.g.johns...@gmail.com> wrote: > On Wed, Mar 2, 2016 at 4:03 PM, avi Singh > wrote: > >> Guys if anyone of you upgraded Prod database to 9.5.1 have any feedback >> please share. I have to plan

Re: [GENERAL] pg_upgrade 9.5.1: pg_upgrade_support missing

schoetbi schoetbi writes: > i tried to migrate a database cluster from pg 9.4.1 to 9.5.1 with > pg_upgrade. I got the follwing error: >> Could not load library "$libdir/pg_upgrade_support" Hmm, pg_upgrade_support isn't a separate library anymore; it's been merged into

Re: [GENERAL] Postgresql upgrade 9.5

On Wed, Mar 2, 2016 at 4:03 PM, avi Singh wrote: > Guys if anyone of you upgraded Prod database to 9.5.1 have any feedback > please share. I have to plan an upgrade, still debating if i should wait > for 9.5.2 or not? Whats your take on that? > Upgrade from what?​

[GENERAL] CStringGetTextDatum and other conversions in server-side code

Writing a language handler: pl_language_handler. Need to do a variety of data conversions. One of them is char* C-string to and from Text/Varchar. The include file postgres.h has the macro CStringGetDatum but this is of no use: it’s just a cast. There is a builtin macro

Re: [GENERAL] Issue enabling track_counts to launch autovacuum in 9.4.5

On Wed, Mar 2, 2016 at 3:49 PM, Tom Lane wrote: > Derek Elder writes: > > That was indeed the root cause. The /etc/hosts file on the server had > > incorrect permissions which caused localhost to not resolve. > > It strikes me that this should not have

[GENERAL] Postgresql upgrade 9.5

Guys if anyone of you upgraded Prod database to 9.5.1 have any feedback please share. I have to plan an upgrade, still debating if i should wait for 9.5.2 or not? Whats your take on that? Thanks Avi

Re: [GENERAL] Issue enabling track_counts to launch autovacuum in 9.4.5

Derek Elder writes: > That was indeed the root cause. The /etc/hosts file on the server had > incorrect permissions which caused localhost to not resolve. It strikes me that this should not have been so hard to solve. The stats collector was trying to tell you what was

Re: [GENERAL] Issue enabling track_counts to launch autovacuum in 9.4.5

The root cause ended up being an /etc/hosts file with incorrect permissions, but I'll file this command away in the knowledge base. Thanks for the assist Alvaro! Derek On Wed, Mar 2, 2016 at 1:36 PM, Alvaro Herrera wrote: > Derek Elder wrote: > > > From what I had

Re: [GENERAL] Issue enabling track_counts to launch autovacuum in 9.4.5

That was indeed the root cause. The /etc/hosts file on the server had incorrect permissions which caused localhost to not resolve. Going to file this away in the knowledge base. Thank you so much for the help David! Derek On Wed, Mar 2, 2016 at 1:37 PM, David G. Johnston <

Re: [GENERAL] Slow Query - Postgres 9.2

On 3 March 2016 at 10:33, Vitaly Burovoy wrote: > On 3/2/16, drum.lu...@gmail.com wrote: > > Hi all... > > > > I'm working on a Slow Query. It's faster now (It was 20sec before) but > > still not good. > > > > Can you have a look and see if you

Re: [GENERAL] Issue enabling track_counts to launch autovacuum in 9.4.5

On Wed, Mar 2, 2016 at 2:29 PM, Derek Elder wrote: > > 2016-03-02 14:58:09 EST [14366]: [8-1] LOG: could not resolve > "localhost": Name or service not known > 2016-03-02 14:58:09 EST [14366]: [9-1] LOG: disabling statistics > collector for lack of working socket > I'm

Re: [GENERAL] Issue enabling track_counts to launch autovacuum in 9.4.5

Derek Elder wrote: > From what I had read, this setting should be on by default. When I checked > our other servers I see that track_counts is on and the autovacuum process > is working correctly on them. Indeed we don't even have the setting > explicitly listed in our postgresql.conf on these

Re: [GENERAL] Slow Query - Postgres 9.2

On 3/2/16, drum.lu...@gmail.com wrote: > Hi all... > > I'm working on a Slow Query. It's faster now (It was 20sec before) but > still not good. > > Can you have a look and see if you can find something? > Cheers > > Query: > > WITH jobs AS ( > ... > FROM >

[GENERAL] Issue enabling track_counts to launch autovacuum in 9.4.5

Good day, (I apologize if this isn't the right place for this, I haven't used the mailing list before and I'm not a Postgres expert.) We've run into an issue where autovacuum is not running on one of our servers using 9.4.5. We discovered that track_counts appears to be off: 2016-03-02

Re: [GENERAL] RLS on catalog tables would be helpful

On 03/02/2016 12:14 PM, Julien Rouhaud wrote: > On 02/03/2016 20:56, Joe Conway wrote: >> I thought there was once a link somewhere on the mail archives to get a >> specific email resent, but for the life of me I cannot find it today :-/ >> > > It's only available in majordomo AFAIK. For instance

[GENERAL] Slow Query - Postgres 9.2

Hi all... I'm working on a Slow Query. It's faster now (It was 20sec before) but still not good. Can you have a look and see if you can find something? Cheers Query: WITH jobs AS ( SELECT job.id, job.clientid, CONCAT(customer.company, ' ', customer.name_first, ' ',

Re: [GENERAL] pg_upgrade 9.5.1: pg_upgrade_support missing

On 03/02/2016 01:42 AM, schoetbi schoetbi wrote: Hello, i tried to migrate a database cluster from pg 9.4.1 to 9.5.1 with pg_upgrade. I got the follwing error: c:\Temp>"C:\Program Files\PostgreSQL\9.5\bin\pg_upgrade" -b "C:\Program Files\PostgreSQL\9.4\bin" -B "C:\Program

Re: [GENERAL] RLS on catalog tables would be helpful

On 02/03/2016 20:56, Joe Conway wrote: > On 03/02/2016 11:53 AM, Joshua D. Drake wrote: >> On 03/02/2016 11:37 AM, Joe Conway wrote: >> >>>

Re: [GENERAL] RLS on catalog tables would be helpful

Hi 2016-03-02 20:56 GMT+01:00 Joe Conway : > On 03/02/2016 11:53 AM, Joshua D. Drake wrote: > > On 03/02/2016 11:37 AM, Joe Conway wrote: > > > >> >

Re: [GENERAL] RLS on catalog tables would be helpful

On 03/02/2016 11:56 AM, Joe Conway wrote: On 03/02/2016 11:53 AM, Joshua D. Drake wrote: On 03/02/2016 11:37 AM, Joe Conway wrote:

Re: [GENERAL] RLS on catalog tables would be helpful

On 03/02/2016 11:53 AM, Joshua D. Drake wrote: > On 03/02/2016 11:37 AM, Joe Conway wrote: > >> http://www.postgresql.org/message-id/flat/CA+Tgmoa=4vti1hb1hta0+qbzlojkpjbd5dkvw3zmp-kdwje...@mail.gmail.com#CA+Tgmoa=4vti1hb1hta0+qbzlojkpjbd5dkvw3zmp-kdwje...@mail.gmail.com >> >> It would be good

Re: [GENERAL] RLS on catalog tables would be helpful

On 03/02/2016 11:37 AM, Joe Conway wrote: http://www.postgresql.org/message-id/flat/CA+Tgmoa=4vti1hb1hta0+qbzlojkpjbd5dkvw3zmp-kdwje...@mail.gmail.com#CA+Tgmoa=4vti1hb1hta0+qbzlojkpjbd5dkvw3zmp-kdwje...@mail.gmail.com It would be good for you to add your thoughts on your use case and specific

Re: [GENERAL] Export binary data - PostgreSQL 9.2

On 29 February 2016 at 06:31, Steve Crawford wrote: > What exactly are you trying to do? Dump/backup your data (e.g. pg_dump)? > Read binary data from a table? If so, what field type (bytea, blob, ...)? > Export to where? > > Cheers, > Steve > > > On Sun, Feb 28,

Re: [GENERAL] RLS on catalog tables would be helpful

On 03/02/2016 11:29 AM, Alan Droege wrote: > I have removed SELECT rights from the pg_proc.prosrc column so that > I can hide the source code of stored functions. This is working OK, > however I would really like to just hide certain functions via RLS. > I understand that great damage could be

[GENERAL] RLS on catalog tables would be helpful

I have removed SELECT rights from the pg_proc.prosrc column so that I can hide the source code of stored functions. This is working OK, however I would really like to just hide certain functions via RLS. I understand that great damage could be done to the system catalog by allowing users to

Re: [GENERAL]

On 03/02/2016 11:13 AM, Alan Droege wrote: subscribe The above will need to be done here: http://www.postgresql.org/mailpref/pgsql-general -- Adrian Klaver adrian.kla...@aklaver.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your

[GENERAL]

subscribe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] $user namespace with pg_dump?

On 03/02/2016 09:06 AM, Mark E. Haase wrote: I can `SET search_path TO "$user",foo,bar,public` and the first path element will expand to the current user. Can I do the same for `pg_dump -n`? I've tried many variations but none of them appear to work: pg_dump -U myuser -n

Re: [GENERAL] How to ensure that a stored function always returns TRUE or FALSE?

Hi 2016-03-02 19:31 GMT+01:00 Alexander Farber : > Thank you all for the valuable replies. > > I've also got suggestions to use IS NOT DISTINCT FROM or STRICT at >

Re: [GENERAL] How to ensure that a stored function always returns TRUE or FALSE?

Thank you all for the valuable replies. I've also got suggestions to use IS NOT DISTINCT FROM or STRICT at http://stackoverflow.com/questions/35742865/how-to-ensure-that-a-stored-function-always-returns-true-or-false but the former has the edge case of NULL=NULL returning TRUE and with the

[GENERAL] $user namespace with pg_dump?

I can `SET search_path TO "$user",foo,bar,public` and the first path element will expand to the current user. Can I do the same for `pg_dump -n`? I've tried many variations but none of them appear to work: pg_dump -U myuser -n '($user|foo|bar|public)' ... pg_dump -U myuser -n

Re: [GENERAL] bloated postgres data folder, clean up

Hey, this is quite the *opposite*. The function find files in the postgres database folder that are not used by the database. To use it : * connect to the database you want to analyse ( **mandatory** ). * create the function (execute function definition) * Execute `SELECT * FROM

Re: [GENERAL] How to ensure that a stored function always returns TRUE or FALSE?

On Wed, Mar 2, 2016 at 5:39 AM, Alexander Farber wrote: > Thanks Vitaly, but instead of inverting the IF-condition I would prefer to > make my function more robust, since it is kind of security-related and I > might forget about the special IF-condition later when

Re: [GENERAL] Looking for pure C function APIs for server extension: language handler and SPI

From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of John R Pierce Sent: Wednesday, 2 March 2016 1:30 PM To: pgsql-general@postgresql.org Subject: Re: [GENERAL] Looking for pure C function APIs for server extension: language handler and SPI On

Re: [GENERAL] substring on bit(n) and bytea types is slow

On 2 March 2016 at 00:33, Arjen Nienhuis wrote: > > On Feb 29, 2016 22:26, "Evgeny Morozov" < > evgeny.morozov+list+pg...@shift-technology.com> wrote > > SELECT substring(bitarray from (32 * (n - 1) + 1) for 32) -- bitarray is > a column of type bit(6400) > > FROM

[GENERAL] pg_upgrade 9.5.1: pg_upgrade_support missing

Hello, i tried to migrate a database cluster from pg 9.4.1 to 9.5.1 with pg_upgrade. I got the follwing error: c:\Temp>"C:\Program Files\PostgreSQL\9.5\bin\pg_upgrade" -b "C:\Program > Files\PostgreSQL\9.4\bin" -B "C:\Program Files\PostgreSQL\9.5\bin" - > d "C:\Program Files\PostgreSQL\9.4\data"

Re: [GENERAL] Looking for pure C function APIs for server extension: language handler and SPI

From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of John McKown Sent: Wednesday, 2 March 2016 1:03 PM To: da...@andl.org Cc: pgsql-general-owner+M220260=david=andl@postgresql.org; Postgres General Subject: Re:

Re: [GENERAL] substring on bit(n) and bytea types is slow

On 2 March 2016 at 00:33, Arjen Nienhuis wrote: > > On Feb 29, 2016 22:26, "Evgeny Morozov" < > evgeny.morozov+list+pg...@shift-technology.com> wrote > > SELECT substring(bitarray from (32 * (n - 1) + 1) for 32) -- bitarray is > a column of type bit(6400) > > FROM

Re: [GENERAL] How to ensure that a stored function always returns TRUE or FALSE?

On 3/2/16, Alexander Farber wrote: > On Wed, Mar 2, 2016 at 11:09 AM, Vitaly Burovoy > wrote: > >> On 3/2/16, Alexander Farber wrote: >> > >> > CREATE OR REPLACE FUNCTION check_user( >> >

Re: [GENERAL] How to ensure that a stored function always returns TRUE or FALSE?

On Wed, Mar 2, 2016 at 3:45 AM, Alexander Farber wrote: > Good morning, > > with the following stored function I would like to validate user data: > > CREATE OR REPLACE FUNCTION check_user( > in_social integer, > in_sid

Re: [GENERAL] bloated postgres data folder, clean up

Hi Remi! This SQL function you have provided, seems to return all valid files, is that correct? In my case, it returned all my ‘base/’ files. Is that normal? If yes, maybe you rename the function to ‘find_useful_postgres_files’ ;-) Could you explain in steps how to use this function to make a

Re: [GENERAL] bloated postgres data folder, clean up

Would gladly do it, but still this "wiki cooloff" stuff, can't create a page Cheers, Rémi-C 2016-02-29 20:44 GMT+01:00 Alvaro Herrera : > Rémi Cura wrote: > > Hey dear list, > > after a fex years of experiments and crash, > > I ended up with a grossly bloated postgres

Re: [GENERAL] How to ensure that a stored function always returns TRUE or FALSE?

Thanks Vitaly, but instead of inverting the IF-condition I would prefer to make my function more robust, since it is kind of security-related and I might forget about the special IF-condition later when using it elsewhere... On Wed, Mar 2, 2016 at 11:09 AM, Vitaly Burovoy

[GENERAL] "missing chunk number XX for toast value YY in pg_toast ..." after pg_basebackup.

Hi All, we are running postgresql 9.1.15 on Debian. we are, basically, running a postgresql cluster with two nodes. We are using synchronous streaming replication to make sure that the slave is always fully in sync (using a recovery.conf that points out the master). The slave is mainly used

Re: [GENERAL] Does RAISE EXCEPTION rollback previous commands in a stored function?

Alexander Farber wrote: > On Wed, Mar 2, 2016 at 11:18 AM, Albe Laurenz wrote: >> You cannot use START TRANSACTION, BEGIN, SAVEPOINT, COMMIT or ROLLBACK >> inside a function. A function always runs within one transaction. >> >> Savepoints or subtransactions are written

Re: [GENERAL] Does RAISE EXCEPTION rollback previous commands in a stored function?

Thank you, this is very helpful, just 1 little question: Why do you write just EXCEPTION? Shouldn't it be RAISE EXCEPTION? Regards Alex On Wed, Mar 2, 2016 at 11:18 AM, Albe Laurenz wrote: > Alexander Farber wrote: > > how to set such a savepoint inside of a stored

Re: [GENERAL] Looking for pure C function APIs for server extension: language handler and SPI

From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of John McKown Sent: Wednesday, 2 March 2016 1:03 PM To: da...@andl.org Cc: pgsql-general-owner+M220260=david=andl@postgresql.org; Postgres General Subject: Re:

Re: [GENERAL] Does RAISE EXCEPTION rollback previous commands in a stored function?

Alexander Farber wrote: > how to set such a savepoint inside of a stored function? > > Can I call "START TRANSACTION", and then at some point later in the same > stored function call RAISE > EXCEPTION? I realize that what I wrote must be confusing. You cannot use START TRANSACTION, BEGIN,

Re: [GENERAL] Does RAISE EXCEPTION rollback previous commands in a stored function?

Hi 2016-03-02 10:47 GMT+01:00 Alexander Farber : > Hi Laurenz, > > how to set such a savepoint inside of a stored function? > > Can I call "START TRANSACTION", and then at some point later in the same > stored function call RAISE EXCEPTION? > You cannot to do it

Re: [GENERAL] How to ensure that a stored function always returns TRUE or FALSE?

On 3/2/16, Alexander Farber wrote: > Good morning, > > with the following stored function I would like to validate user data: > > CREATE OR REPLACE FUNCTION check_user( > in_social integer, > in_sid varchar(255), >

Re: [GENERAL] Does RAISE EXCEPTION rollback previous commands in a stored function?

Hi Laurenz, how to set such a savepoint inside of a stored function? Can I call "START TRANSACTION", and then at some point later in the same stored function call RAISE EXCEPTION? Regargs Alex On Wed, Mar 2, 2016 at 10:37 AM, Albe Laurenz wrote: > Andreas Kretschmer

[GENERAL] How to ensure that a stored function always returns TRUE or FALSE?

Good morning, with the following stored function I would like to validate user data: CREATE OR REPLACE FUNCTION check_user( in_social integer, in_sid varchar(255), in_auth varchar(32)) RETURNS boolean AS $func$

Re: [GENERAL] Does RAISE EXCEPTION rollback previous commands in a stored function?

Andreas Kretschmer wrote: >> Alexander Farber hat am 1. März 2016 um 19:41 >> geschrieben: >> >> >> Good evening, >> >> in PostgreSQL 9.5 does RAISE EXCEPTION reliably rollback all previous >> commands in a stored function? > > Yes. That is, unless you set a

Re: [GENERAL] could not migrate 8.0.13 database with large object data to 9.5.1

On 3/2/2016 12:29 AM, Magnus Hagander wrote: How large is the total database? The earliest versions of pg on Windows had bugs in pg_dump for files larger than 2GB. I don't recall exactly when they were fixed, but this was a long time ago.. Through if my memory is correct the actual bugs were

Re: [GENERAL] could not migrate 8.0.13 database with large object data to 9.5.1

On Mar 2, 2016 06:01, "John R Pierce" wrote: > > (thread moved from pg_bugs) > (upgrading a 8.0.13 database on Windows XP 32bit to 9.5.1 on Windows 8 64 bit.) > > > On 3/1/2016 8:05 PM, Premsun Choltanwanich wrote: >> >> Modified command by remove -Ft flag as per you