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

[Andres Freund]

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 (version 9.6 I think as
> far as I found in the source, wal_level: logical, max_replication_slot: > 1,
> track_commit_timestamp: on, I am not sure whether this will help or not).
> Following the online documentation, everything works fine until I input
> 
> SELECT * FROM pg_logical_slot_peek_changes('regression_slot', NULL, NULL,
> 'include-timestamp', 'on');
> 
> 
> I always got 1999-12-31 16:00 as the commit time for arbitrary transactions
> with DML statements.
> After several tries, I realize that the txn->commit_time returned was always
> 0.
> Could you help me by indicating me what could be wrong in my case? Any
> missing parameters set?

That was a bug introduced recently (9.5).  The issue was discussed in
http://archives.postgresql.org/message-id/56D42918.1010108%40postgrespro.ru
, and a fix has now been pushed.

Thanks for the report!

Regards,

Andres


-- 
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] bloated postgres data folder, clean up

[Johnny Morano]

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 /tmp/useless_files.csv

# filter out the .## files, e.g.:
# 
48175847.37;/base/16398/48175847.37;/data/postgres/base/16398/48175847.37;1047420928
# 
48175847.36;/base/16398/48175847.36;/data/postgres/base/16398/48175847.36;1073741824
# 
48175847.35;/base/16398/48175847.35;/data/postgres/base/16398/48175847.35;1073741824
# 
48175847.34;/base/16398/48175847.34;/data/postgres/base/16398/48175847.34;1073741824
# 
48175847.33;/base/16398/48175847.33;/data/postgres/base/16398/48175847.33;1073741824
# 
48175847.32;/base/16398/48175847.32;/data/postgres/base/16398/48175847.32;1073741824
# 
48175847.31;/base/16398/48175847.31;/data/postgres/base/16398/48175847.31;1073741824
# 
48175847.30;/base/16398/48175847.30;/data/postgres/base/16398/48175847.30;1073741824
#
# because oid2name doesn't like them, gives error:
# /data/postgres # oid2name -f 48175847.30 -i -S -q -d live
# oid2name: query failed: ERROR:  invalid input syntax for type oid: 
"48175847.30"
# LINE 11:   (pg_catalog.pg_relation_filenode(c.oid) IN ('48175847.30')...
#^
#
# oid2name: query was: SELECT pg_catalog.pg_relation_filenode(c.oid) as 
"Filenode", relname as "Table Name"
# FROM pg_catalog.pg_class c
#  LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
#  LEFT JOIN pg_catalog.pg_database d ON d.datname = 
pg_catalog.current_database(),
#  pg_catalog.pg_tablespace t
# WHERE relkind IN ('r', 'm', 'i', 'S', 't') AND
#t.oid = CASE
#   WHEN reltablespace <> 0 THEN reltablespace
#   ELSE dattablespace
#END AND
#   (pg_catalog.pg_relation_filenode(c.oid) IN ('48175847.30'))
# ORDER BY relname

/data/postgres # cut -d ';' -f1 /tmp/useless_files.csv | cut -d'.' -f1 | sort 
-n | uniq >/tmp/potential_useless_oids.csv
/data/postgres # wc -l /tmp/potential_useless_oids.csv
1017 /tmp/potential_useless_oids.csv

# get a list of all used oids, instead of examing one by one
/data/postgres # oid2name -i -S -q -d live | awk '{print $1}' | sort -n | uniq 
>/tmp/used_oids.csv
/data/postgres # wc -l /tmp/used_oids.csv
940 /tmp/used_oids.csv

/data/postgres # while read i; do grep $i /tmp/used_oids.csv >/dev/null || 
(echo "$i" >>/tmp/not_in_use_oids.csv); done < /tmp/potential_useless_oids.csv
/data/postgres # wc -l /tmp/not_in_use_oids.csv
168 /tmp/not_in_use_oids.csv

/data/postgres # egrep -v 'fsm|vm' /tmp/not_in_use_oids.csv |wc -l
1
/data/postgres # egrep -v 'fsm|vm' /tmp/not_in_use_oids.csv
file_name
# The CSV header only

So, no bloated files.
But, using the function from 
https://wiki.postgresql.org/wiki/Show_database_bloat I get:

live=# select tbloat,wasted_space from table_bloat order by wasted_space desc 
limit 25;
tbloat │ wasted_space
┼──
1.0 │ 9976 kB
1.2 │ 98 GB
1.0 │ 97 MB
1.4 │ 96 kB
1.2 │ 920 kB
1.2 │ 88 kB
1.1 │ 88 kB
2.0 │ 8192 bytes
0.0 │ 8192 bytes
1.3 │ 8192 bytes
2.0 │ 8192 bytes
1.3 │ 8192 bytes
1.5 │ 8192 bytes
1.5 │ 8192 bytes
2.0 │ 8192 bytes
1.1 │ 8192 bytes
1.0 │ 8192 bytes
1.1 │ 8192 bytes
1.3 │ 8192 bytes
1.5 │ 8192 bytes
1.1 │ 80 kB
1.0 │ 7584 kB
1.6 │ 71 MB
1.0 │ 704 kB
1.1 │ 6968 kB
(25 rows)

So actually, quite a lot of bloated data ☺
What am I doing wrong?

Mit freundlichen Grüßen / With kind regards,
Johnny Morano


Johnny Morano  |  Principal Systems Engineer

PAY.ON GmbH  |  AN ACI WORLDWIDE COMPANY  |  
WWW.PAYON.COM
Jakob-Haringer-Str. 1  |  5020 Salzburg  |  Austria

This email message and any attachments may contain confidential, proprietary or 
non-public information. This information is intended solely for the designated 
recipient(s). If an addressing or transmission error has misdirected this 
email, please notify the sender immediately and destroy this email. Any review, 
dissemination, use or reliance upon this information by unintended recipients 
is prohibited. Any opinions expressed in this email are those of the author 
personally.

From: Rémi Cura [mailto:remi.c...@gmail.com]
Sent: Mittwoch, 2. März 2016 17:49
To: Johnny Morano
Cc: Alvaro Herrera; PostgreSQL General
Subject: 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 find_useless_postgres_file('your_database_name')`

This will output a list of files that are 

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

[John R Pierce]

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 encoding, and null is just 
another character.


single and double real/floats are stored in standard x86 IEEE floating 
point.


the date and time types are all fixed lengths, mostly a long int or two.

numeric in postgres is a bit tricky.  its a decimal number stored in 
base 1 as a series of short integers, and can represent.   does your 
language even have a decimal or BCD or something numeric type?




--
john r pierce, recycling bits in santa cruz



--
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] CStringGetTextDatum and other conversions in server-side code

[david]

> [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, thanks, I found those. Most useful too. What I was looking for was 
the preferred set of functions that could be used to convert between those 
types and native C types (that could be used externally with no Postgres 
includes).

[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).

Regards
David M Bennett FACS

Andl - A New Database Language - andl.org







-- 
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] Re: could not migrate 8.0.13 database with large object data to 9.5.1

[John R Pierce]

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 all processes are completed.



your logfile shows a bunch of custom functions being defined which are 
dependent on a missing binary library, apparently your previous install 
of postgres has some customizations.



2016-03-02 18:06:25 ICT ERROR:  could not find function "lo_in" in file "C:/Program 
Files/PostgreSQL/9.5/lib/lo.dll"
2016-03-02 18:06:25 ICT STATEMENT:  CREATE FUNCTION lo_in(cstring) RETURNS lo
LANGUAGE c IMMUTABLE STRICT
AS '$libdir/lo', 'lo_in';


these custom functions are being used to define a custom data type.


2016-03-02 18:06:25 ICT ERROR:  function lo_in(cstring) does not exist
2016-03-02 18:06:25 ICT STATEMENT:  CREATE TYPE lo (
INTERNALLENGTH = 4,
INPUT = lo_in,
OUTPUT = lo_out,
ALIGNMENT = int4,
STORAGE = plain
);



and your tables are using this custom data type 'lo', which is invalid 
since the code to implement it is missing.



2016-03-02 18:06:26 ICT ERROR:  type "lo" is only a shell at character 186
2016-03-02 18:06:26 ICT STATEMENT:  CREATE TABLE t_familypic (
sysid bigint DEFAULT nextval('public.t_familypic_sysid_seq'::text) 
NOT NULL,
mbrsysid bigint NOT NULL,
familysysid bigint NOT NULL,
familypic lo
);



do you have the source code to this lo.dll so you can be rebuild it for 
9.5 ?






--
john r pierce, recycling bits in santa cruz



--
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] CStringGetTextDatum and other conversions in server-side code

[Joe Conway]

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 should know that quite a few of the more experienced
people on these lists will totally ignore your HTML emails, so you do
yourself no favors by not following that advice.

> [dmb>] I have the source tree, I use grep and other tools, and I've
> read thousands of lines of code. There is a great deal of
> inconsistency, and much of the code looks like "Summer of Code", or
> has been written a number of years ago or talks about issues I know
> have been resolved, or says it's just 'legacy support'. The upshot
> is: I haven't yet been able to figure out the 'right' way to do
> things, or what might go wrong if I choose the 'wrong' way.


There is no specific "right" way per se. The postgres code base has been
evolving for going on 30 years, so yes, there are inconsistencies. As I
said, best advice is to find code similar to whatever you are trying to
achieve and emulate it.


> [dmb>] That was my plan. But I do prefer to emulate code that is
> 'right'.

If it is in the postgres source tree (i.e. contrib and src/pl) you can
consider it to be about as "right" as you can get because it is all
maintained to be that way. If it is an external project you'll have to
be more careful.

> [dmb>] So how would I go about finding a set of useful conversion
> functions for basic types (real, decimal, time, etc)?

Sorry I don't have a better answer than before: grep and/or read source
for other PLs.

FWIW here are examples from PL/R for incoming (argument) and outgoing
(result) conversions of scalar values:

  https://github.com/jconway/plr/blob/master/pg_conversion.c#L632
  https://github.com/jconway/plr/blob/master/pg_conversion.c#L1002

That same file also has routines for conversions of more complex data types.

Joe

-- 
Crunchy Data - http://crunchydata.com
PostgreSQL Support for Secure Enterprises
Consulting, Training, & Open Source Development



signature.asc
Description: OpenPGP digital signature

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

[Premsun Choltanwanich]

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 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 suggestion:
>> pg_dump -v -h 192.168.200.75 -U clubadmin -d clubprogram | psql -U clubadmin 
>> -d clubprogram
>>
>> Result (got same message even with parameter -b or not):
>> pg_dump: reading rewrite rules
>> pg_dump: reading policies
>> pg_dump: reading large objects
>> ...
>>
>> pg_dump: creating FUNCTION "public.plpgsql_call_handler()"
>> pg_dump: creating FUNCTION "public.plpgsql_validator(oid)"
>> ...
>>
>> pg_dump: creating FUNCTION "public.pg_file_write(text, text, boolean)"
>
>
> those all sound like standard postgres functions, its not clear to me why 
> pg_dump is generating the CREATE FUNCTION code for them.
>
>
>> pg_dump: [archiver] could not write to output file: Invalid argument
>>
>
> ok, presumably your new box has plenty of disk space? try this on the new 9.5 
> system...
>
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 in pg_dump itself, so using a new pg_dump against the old 
server should be safe. 
/Magnus 

Re: [GENERAL] Export binary data - PostgreSQL 9.2

[Abdul Sayeed]

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 1:21 AM, drum.lu...@gmail.com 
wrote:

>
>
> On 29 February 2016 at 06:31, Steve Crawford <
> scrawf...@pinpointresearch.com> 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, 2016 at 9:12 AM, drum.lu...@gmail.com <
>> drum.lu...@gmail.com> wrote:
>>
>>> Hi all,
>>>
>>>
>>> Which command would be to export the binary data for a table?
>>>
>>> I was unable to find it...
>>>
>>> Thanks
>>>
>>
>>
>
> Have just used the COPY  WITH Binary
>
> Thank you.
>



-- 
Thanks & Regards,
Abdul Sayeed
PostgreSQL DBA
Postgres Professional Certified
EnterpriseDB Corp
Skype: abdul.sayeed24

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

[John R Pierce]

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, 
http://www.postgresql.org/docs/current/static/xfunc-c.html#XFUNC-C-TYPE-TABLE




--
john r pierce, recycling bits in santa cruz



--
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] CStringGetTextDatum and other conversions in server-side code

[david]

(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 function?
> 2.   Is it OK to use, or are there restrictions?
> 3.   Does it have friends: are there other conversion functions like
> this for other data types (decimal, time in particular)?
> 4.   Is there any particular documentation I can read?

Your best bet is to look at examples. The code in the source tree under contrib 
has many examples, and more specifically src/pl/* should have even more 
relevant examples. Remember, grep is your friend -- get a copy of the source 
tree and try:
[dmb>] 
[dmb>] I have the source tree, I use grep and other tools, and I've read 
thousands of lines of code. There is a great deal of inconsistency, and much of 
the code looks like "Summer of Code", or has been written a number of years ago 
or talks about issues I know have been resolved, or says it's just 'legacy 
support'. The upshot is: I haven't yet been able to figure out the 'right' way 
to do things, or what might go wrong if I choose the 'wrong' way. 

Basically any symbol exported from the postgres backend can be used by your PL. 
There is not a lot of documentation other than in the source code itself. Look 
at 
examples, see what they do, emulate it.
[dmb>] That was my plan. But I do prefer to emulate code that is 'right'.

[dmb>] So how would I go about finding a set of useful conversion functions for 
basic types (real, decimal, time, etc)?

Regards
David M Bennett FACS

Andl - A New Database Language - andl.org







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

[GENERAL] BDR concern/issue

[cchee-ob]

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
(bdr_16385_6228994276814368133_1_16384).  Any suggestions?

svp2=# SELECT   
  slot_name, database, active,
  pg_xlog_location_diff(pg_current_xlog_insert_location(), restart_lsn)
AS retained_bytes
FROM pg_replication_slots
WHERE plugin = 'bdr';
slot_name| database | active |
retained_bytes 
-+--++
 bdr_16385_6206441431541275808_1_16385__ | svp2 | f  |  
410036551440
 bdr_16385_6228994276814368133_1_16384__ | svp2 | t  |
285760
(2 rows)

svp2=# SELECT * FROM pg_stat_replication;
-[ RECORD 1 ]+---
pid  | 1122
usesysid | 10
usename  | postgres
application_name | bdr (6228994276814368133,1,16384,):receive
client_addr  | 10.253.0.8
client_hostname  | 
client_port  | 43724
backend_start| 2016-02-25 17:53:21.10519-08
backend_xmin | 
state| streaming
sent_location| 184/AE210BC8
write_location   | 184/AE210BC8
flush_location   | 184/AE20E748
replay_location  | 184/AE210BC8
sync_priority| 0
sync_state   | async

svp2=# select * from bdr.bdr_node_slots;
 node_name | slot_name 
---+---
(0 rows)

svp2=# SELECT * FROM bdr.bdr_nodes;
-[ RECORD 1 ]--+--
node_sysid | 6206439726032130602
node_timeline  | 1
node_dboid | 16385
node_status| r
node_name  | BDR1
node_local_dsn | host=10.253.228.105 port=5432 dbname=svp2
node_init_from_dsn | 
-[ RECORD 2 ]--+--
node_sysid | 6206440469625465777
node_timeline  | 1
node_dboid | 16385
node_status| k
node_name  | BDR2
node_local_dsn | host=10.253.16.25 port=5432 dbname=svp2
node_init_from_dsn | host=10.253.228.105 port=5432 dbname=svp2





--
View this message in context: 
http://postgresql.nabble.com/BDR-concern-issue-tp5890301.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


-- 
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] CStringGetTextDatum and other conversions in server-side code

[Joe Conway]

(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.
> 
> 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 CStringGetTextDatum which calls a function and
> looks like it might work. Questions:
> 
> 1.   Is this the right function?
> 2.   Is it OK to use, or are there restrictions?
> 3.   Does it have friends: are there other conversion functions like
> this for other data types (decimal, time in particular)?
> 4.   Is there any particular documentation I can read?

Your best bet is to look at examples. The code in the source tree under
contrib has many examples, and more specifically src/pl/* should have
even more relevant examples. Remember, grep is your friend -- get a copy
of the source tree and try:

cd 
grep -rni CStringGetTextDatum src/pl/* --include=*.c
grep -rni CStringGetTextDatum contrib/* --include=*.c
grep -rni TextDatumGetCstring contrib/* --include=*.c

Additionally there are external projects that implement postgres
procedural language handlers, e.g.:
  https://github.com/jconway/plr
Lots of good examples there too. Google for others.

Basically any symbol exported from the postgres backend can be used by
your PL. There is not a lot of documentation other than in the source
code itself. Look at examples, see what they do, emulate it.

HTH,

Joe

-- 
Crunchy Data - http://crunchydata.com
PostgreSQL Support for Secure Enterprises
Consulting, Training, & Open Source Development



signature.asc
Description: OpenPGP digital signature

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

[David G. Johnston]

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 disimprovement, because in many common
> configurations WARNING messages don't appear in the postmaster log at all.
> In fact, I'd say it's a bug that the "autovacuum not started" message is
> emitted as a WARNING not LOG.
>
> Maybe we need some way of printing something at LOG priority but having
> the printed text be WARNING.  I'm afraid that might cause about as much
> confusion as it would solve, though.
>

​Yes, I recall this unusual situation now...​

In this specific case, when we know that "WARNING: autovacuum not started
because of misconfiguration" was emitted, if the previous two messages were
also at WARNING would they have been emitted as well?


> > It probably would help to specify, if known, whether the suspected
> > mis-configuration is external or internal to PostgreSQL - i.e., do I need
> > to fix postgres.conf or is something external (like the hosts file) to
> > blame.
>
> In the case of a name resolution failure, the problem is certainly
> external to Postgres, but we don't have enough information to say more
> than that.  We could print a hint guessing at causes (like broken
> /etc/host or /etc/resolv.conf files), but it would be guesses --- and
> I'm afraid there's enough cross-system variation in the way this stuff is
> configured that any hint would be likely to just be misleading.
>

​I was trying to restrict it to simply internal/external though - I
wouldn't care where the resolution comes other than we known that nothing
in PostgreSQL is involved as a server, only as a client.​  So saying "its
not our fault" seems appropriate and sufficient so the user doesn't spend
time with ALTER SYSTEM or editing the configuration file.


> > This is getting a bit deep for a rare problem like this - I think that
> > making ​the root messages WARNING (or ERROR)
>
> ERROR would mean that the postmaster fails to start at all.  That doesn't
> seem like an improvement either.
>
>
Its only a problem if the postmaster starts and we emit error...​do we have
FATAL that could imply the postmaster doesn't start and use ERROR if one of
the optional related processes (statistics, auto-vacuum) doesn't start?

​David J.
​

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

[Tom Lane]

"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 the postmaster log at all.
In fact, I'd say it's a bug that the "autovacuum not started" message is
emitted as a WARNING not LOG.

Maybe we need some way of printing something at LOG priority but having
the printed text be WARNING.  I'm afraid that might cause about as much
confusion as it would solve, though.

> It probably would help to specify, if known, whether the suspected
> mis-configuration is external or internal to PostgreSQL - i.e., do I need
> to fix postgres.conf or is something external (like the hosts file) to
> blame.

In the case of a name resolution failure, the problem is certainly
external to Postgres, but we don't have enough information to say more
than that.  We could print a hint guessing at causes (like broken
/etc/host or /etc/resolv.conf files), but it would be guesses --- and
I'm afraid there's enough cross-system variation in the way this stuff is
configured that any hint would be likely to just be misleading.

> This is getting a bit deep for a rare problem like this - I think that
> making ​the root messages WARNING (or ERROR)

ERROR would mean that the postmaster fails to start at all.  That doesn't
seem like an improvement either.

regards, tom lane


-- 
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] Issue enabling track_counts to launch autovacuum in 9.4.5

[Joshua D. Drake]

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 14:58:09 EST [14366]: [10-1] WARNING: autovacuum not started because 
of misconfiguration
2016-03-02 14:58:09 EST [14366]: [11-1] HINT: Enable the "track_counts" option.


Or both changes, or something else entirely?

I'd be interested to hear how you perceived these log messages and
what you think might help the next person.


I can tell you that as I read them, they meant nothing and I know what 
they mean. This is why:


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


Why would I care about a "LOG" message. Generally speaking they don't 
tell us anything useful. If, it was:


ERROR: could not resolve "localhost" ...
ERROR: Disabling statistics collector 

It would mean a whole lot more. The second set makes sense to me but 
what isn't clear is that the first one is really an error that can cause 
the warnings of the second.


I do think that it should be an error if we are disabling statistics due 
to something being wrong. We can't really operate properly without them.


Sincerely,

JD

--
Command Prompt, Inc.  http://the.postgres.company/
+1-503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Everyone appreciates your honesty, until you are honest with them.


--
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] Postgresql upgrade 9.5

[avi Singh]

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 an upgrade, still debating if i should wait
>> for 9.5.2 or not? Whats your take on that?
>>
>
> Upgrade from what?​
>
> ​There is a somewhat big
> ​ (i.e., queries returning the wrong results)​
> , if not that likely to be encountered, bug in 9.5.1 that makes me advise
> that you wait until 9.5.2
>
> David J.
>

Re: [GENERAL] pg_upgrade 9.5.1: pg_upgrade_support missing

[Tom Lane]

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 core.  I would not have expected that to cause any problems, because
the functions in that library shouldn't have been referenced in your old
database in the first place.  They were supposed to be installed and then
removed again during the process of (prior versions of) pg_upgrade.

I'm guessing you had leftovers from old failed pg_upgrade attempts that
you never completed.

What you want to do is simply manually drop the functions referencing
$libdir/pg_upgrade_support.  A look into the pg_upgrade sources suggests
that this ought to do it:

DROP SCHEMA IF EXISTS binary_upgrade CASCADE;

though you'll probably have to do that in every database of the
installation.

regards, tom lane


-- 
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] Postgresql upgrade 9.5

[David G. Johnston]

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

​There is a somewhat big
​ (i.e., queries returning the wrong results)​
, if not that likely to be encountered, bug in 9.5.1 that makes me advise
that you wait until 9.5.2

David J.

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

[david]

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 CStringGetTextDatum which calls a function and looks 
like it might work. Questions:

1.   Is this the right function?

2.   Is it OK to use, or are there restrictions?

3.   Does it have friends: are there other conversion functions like this 
for other data types (decimal, time in particular)?

4.   Is there any particular documentation I can read?

 

Regards

David M Bennett FACS

  _  

Andl - A New Database Language - andl.org

 

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

[David G. Johnston]

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 been so hard to solve.  The
> stats collector was trying to tell you what was wrong, but evidently
> you could not interpret those messages correctly.  I am thinking that
> we need to do some work on the message wording; or maybe there is one
> more message that needs to be emitted so you can follow the causal
> chain?
>
> In particular, perhaps it wasn't immediately obvious that the first
> of these messages was the cause of the second:
>
> > 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
>
> in which case maybe we could rephrase the first message along the
> lines of "could not resolve "localhost" to establish statistics
> collector socket: ".  (There are a few other
> messages in the same area that would need to be changed similarly.)
>
> 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 14:58:09 EST [14366]: [10-1] WARNING: autovacuum not started
> because of misconfiguration
> > 2016-03-02 14:58:09 EST [14366]: [11-1] HINT: Enable the "track_counts"
> option.
>
> Or both changes, or something else entirely?
>
> I'd be interested to hear how you perceived these log messages and
> what you think might help the next person.
>

​The fact that the first two are only LOG level and not WARNING would seems
like the easiest improvement to make.  I had the benefit of basically
knowing track_counts was a red-herring given the provided context so I went
and started looking at anything preceding the first warning that could give
me a hint as to the nature of the "misconfiguration".

It probably would help to specify, if known, whether the suspected
mis-configuration is external or internal to PostgreSQL - i.e., do I need
to fix postgres.conf or is something external (like the hosts file) to
blame.  In this case since we don't control "localhost" it would be
"external misconfiguration".

This also doesn't help:
show autovacuum;
autovacuum

on

Why do we indirectly disable autovacuum via disabling one of its required
parameters instead of just disabling the main property.  I don't suppose we
can add a third option (on, off, broken) to this which would allow
distinguishing between a user-specified condition (off) and a system
imposed one (broken).

This is getting a bit deep for a rare problem like this - I think that
making ​the root messages WARNING (or ERROR) instead of info (and ideally
linking the two explicitly if possible) would have the desired effect of
pointing the user to the first thing they need to fix - and assume they
would ignore all subsequent messages (and hints) until the first one is
handled (i.e. use good trouble-shooting practices).  The hint and the
change to track_counts then becomes a non-issue.


David J.

[GENERAL] Postgresql upgrade 9.5

[avi Singh]

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

[Tom Lane]

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 wrong, but evidently
you could not interpret those messages correctly.  I am thinking that
we need to do some work on the message wording; or maybe there is one
more message that needs to be emitted so you can follow the causal
chain?

In particular, perhaps it wasn't immediately obvious that the first
of these messages was the cause of the second:

> 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

in which case maybe we could rephrase the first message along the
lines of "could not resolve "localhost" to establish statistics
collector socket: ".  (There are a few other
messages in the same area that would need to be changed similarly.)

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 14:58:09 EST [14366]: [10-1] WARNING: autovacuum not started 
> because of misconfiguration
> 2016-03-02 14:58:09 EST [14366]: [11-1] HINT: Enable the "track_counts" 
> option.

Or both changes, or something else entirely?

I'd be interested to hear how you perceived these log messages and
what you think might help the next person.

regards, tom lane


-- 
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] Issue enabling track_counts to launch autovacuum in 9.4.5

[Derek Elder]

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 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 servers.
> >
> > We first tried a simple restart, but that didn't work.
>
> Try
>   ALTER SYSTEM RESET track_counts;
>
> --
> Álvaro Herrerahttp://www.2ndQuadrant.com/
> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>

-- 
This message, and any documents attached hereto, may contain confidential 
or proprietary information intended only for the use of the addressee(s) 
named above or may contain information that is legally  privileged. If you 
are not the intended addressee, or the person responsible for delivering it 
to the intended addressee, you are hereby notified that reading, 
disseminating, distributing or copying this message is strictly prohibited. 
If you have received this message by mistake, please immediately notify us 
by replying to the message and delete the original message and any copies 
immediately thereafter.  Thank you for your cooperation.

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

[Derek Elder]

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 <
david.g.johns...@gmail.com> wrote:

>
> 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 reasonably certain the above is the root of the problem.
> ​
>
>
>>
>> 2016-03-02 14:58:09 EST [14366]: [10-1] WARNING: autovacuum not started
>> because of misconfiguration
>> 2016-03-02 14:58:09 EST [14366]: [11-1] HINT: Enable the "track_counts"
>> option.
>>
>
> ​This is simply a symptom.  The hint is misleading in this situation.
>
> Someone more informed can hopefully offer more guidance fixing your
> localhost ​resolution problem.
>
> David J.
>
>

-- 
This message, and any documents attached hereto, may contain confidential 
or proprietary information intended only for the use of the addressee(s) 
named above or may contain information that is legally  privileged. If you 
are not the intended addressee, or the person responsible for delivering it 
to the intended addressee, you are hereby notified that reading, 
disseminating, distributing or copying this message is strictly prohibited. 
If you have received this message by mistake, please immediately notify us 
by replying to the message and delete the original message and any copies 
immediately thereafter.  Thank you for your cooperation.

Re: [GENERAL] Slow Query - Postgres 9.2

[drum.lu...@gmail.com]

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 can find something?
> > Cheers
> >
> > Query:
> >
> > WITH jobs AS (
> > ...
> > FROM
> > jobs AS job
> > JOIN
> > public.ja_notes AS note
> > ON
> > note.jobid = job.id
> > AND note.note_type IN ('time', 'part')
> > ...
>
> It is the most long part. All query is 8.8sec.
> SeqScan by CTE is 2.8sec! and index scan in ix_notes_jobid_per_type
> 500rows(loops) * 9.878ms!!! = 4.939sec.
>
> Why does it take so long time?
> For example, index scan in ja_customers_pkey is only 0.781 per row...
> 10 times faster!
>
> What definition of the ix_notes_jobid_per_type? Is it bloated?
>
>
> Hi there!

CREATE INDEX
ix_notes_jobid_per_type
ON
ja_notes
(
"jobid",
"note_type"
);


\di+ ix_notes_jobid_per_type

   List of relations

 Schema |  Name   | Type  |  Owner   |  Table   |  Size  |
Description

+-+---+--+--++-

 public | ix_notes_jobid_per_type | index | postgres | ja_notes | 484 MB |



it does not seem to be bloated... since the table is 2805 MB

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

[David G. Johnston]

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 reasonably certain the above is the root of the problem.
​


>
> 2016-03-02 14:58:09 EST [14366]: [10-1] WARNING: autovacuum not started
> because of misconfiguration
> 2016-03-02 14:58:09 EST [14366]: [11-1] HINT: Enable the "track_counts"
> option.
>

​This is simply a symptom.  The hint is misleading in this situation.

Someone more informed can hopefully offer more guidance fixing your
localhost ​resolution problem.

David J.

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

[Alvaro Herrera]

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 servers.
> 
> We first tried a simple restart, but that didn't work.

Try
  ALTER SYSTEM RESET track_counts;

-- 
Álvaro Herrerahttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


-- 
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] Slow Query - Postgres 9.2

[Vitaly Burovoy]

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
> jobs AS job
> JOIN
> public.ja_notes AS note
> ON
> note.jobid = job.id
> AND note.note_type IN ('time', 'part')
> ...

It is the most long part. All query is 8.8sec.
SeqScan by CTE is 2.8sec! and index scan in ix_notes_jobid_per_type
500rows(loops) * 9.878ms!!! = 4.939sec.

Why does it take so long time?
For example, index scan in ja_customers_pkey is only 0.781 per row...
10 times faster!

What definition of the ix_notes_jobid_per_type? Is it bloated?

> Explain analyze link: http://explain.depesz.com/s/IIDj
>


-- 
Best regards,
Vitaly Burovoy


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

[GENERAL] Issue enabling track_counts to launch autovacuum in 9.4.5

[Derek Elder]

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 14:58:09 EST [14366]: [2-1] DEBUG: loaded library "repmgr_funcs"
2016-03-02 14:58:09 EST [14366]: [3-1] DEBUG: SlruScanDirectory invoking
callback on pg_notify/
2016-03-02 14:58:09 EST [14366]: [4-1] DEBUG: removing file "pg_notify/"
2016-03-02 14:58:09 EST [14366]: [5-1] DEBUG: dynamic shared memory system
will support 690 segments
2016-03-02 14:58:09 EST [14366]: [6-1] DEBUG: created dynamic shared memory
control segment 1804289383 (5532 bytes)
2016-03-02 14:58:09 EST [14366]: [7-1] DEBUG: max_safe_fds = 984,
usable_fds = 1000, already_open = 6
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
2016-03-02 14:58:09 EST [14366]: [10-1] WARNING: autovacuum not started
because of misconfiguration
2016-03-02 14:58:09 EST [14366]: [11-1] HINT: Enable the "track_counts"
option.
2016-03-02 14:58:09 EST [14366]: [12-1] LOG: database system is ready to
accept connections
2016-03-02 14:58:10 EST [14366]: [13-1] DEBUG: forked new backend,
pid=14377 socket=8
2016-03-02 14:58:10 EST [14366]: [14-1] DEBUG: server process (PID 14377)
exited with exit code 0
2016-03-02 14:58:10 EST [14366]: [15-1] DEBUG: forked new backend,
pid=14379 socket=8
2016-03-02 14:58:10 EST [14366]: [16-1] DEBUG: server process (PID 14379)
exited with exit code 0
2016-03-02 15:00:01 EST [14366]: [17-1] DEBUG: forked new backend,
pid=14425 socket=8
2016-03-02 15:00:01 EST [14366]: [18-1] DEBUG: server process (PID 14425)
exited with exit code 0
2016-03-02 15:03:26 EST [14366]: [19-1] DEBUG: postmaster received signal 2
2016-03-02 15:03:26 EST [14366]: [20-1] LOG: received fast shutdown request
2016-03-02 15:03:26 EST [14366]: [21-1] LOG: aborting any active
transactions
2016-03-02 15:03:26 EST [14366]: [22-1] DEBUG: cleaning up dynamic shared
memory control segment with ID 1804289383

show autovacuum;
autovacuum

on

show track_counts;
track_counts
--
off


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

We first tried a simple restart, but that didn't work.

Next I attempted to add track_counts = on to the postgresql.conf and reload
it, but the setting doesn't seem to take effect.

I even tried "SET track_counts = on;", but that only works in the current
session and doesn't persist.

Is there any other way of turning this setting on or is there something
somewhere that could be preventing track_counts from being enabled
correctly?

Thank you very much,

Derek

-- 
This message, and any documents attached hereto, may contain confidential 
or proprietary information intended only for the use of the addressee(s) 
named above or may contain information that is legally  privileged. If you 
are not the intended addressee, or the person responsible for delivering it 
to the intended addressee, you are hereby notified that reading, 
disseminating, distributing or copying this message is strictly prohibited. 
If you have received this message by mistake, please immediately notify us 
by replying to the message and delete the original message and any copies 
immediately thereafter.  Thank you for your cooperation.

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

[Joe Conway]

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
> https://lists.postgresql.org/mj/mj_wwwusr?list=pgsql-hackers=on=archive-get-part=201602/753
> 
> once you log in you'll find the "Mail this message to..." link at bottom
> of the page.
> 
>> However, if you view the raw message (there is a link for that on the
>> archives), save it locally, and then open it in your email client, you
>> can then hit "reply-all".

Ah, thanks to all the folks who answered with that -- I knew I had seen
it somewhere. But in any case the raw message method I mentioned works too.

Joe

-- 
Crunchy Data - http://crunchydata.com
PostgreSQL Support for Secure Enterprises
Consulting, Training, & Open Source Development



signature.asc
Description: OpenPGP digital signature

[GENERAL] Slow Query - Postgres 9.2

[drum.lu...@gmail.com]

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, ' ',
customer.name_last) AS "identity",
job.gps_lat,
job.gps_long
FROM public.ja_jobs AS job
JOIN public.ja_customers AS customer ON customer.id = job.customerid
WHERE
job.clientid = 22
AND job.time_job >= 1422702000
AND job.time_job <= 1456743540

AND NOT job.deleted
AND NOT job.templated), items AS (
SELECT
job.identity,
COALESCE(bill_item.unit_cost, billable.unit_cost, 0) *
bill_item.quantity AS cost,
COALESCE(bill_item.unit_price, billable.unit_price, 0) *
bill_item.quantity AS revenue,
bill_item.quantity AS quantity,
note.n_quote_status,
bill_item.for_invoicing
FROM
jobs AS job
JOIN
public.ja_notes AS note
ON
note.jobid = job.id
AND note.note_type IN ('time', 'part')
JOIN
dm.bill_items AS bill_item
ON
bill_item.bill_item_id = note.bill_item_id
AND bill_item.for_invoicing
LEFT JOIN
dm.billables AS billable
ON
billable.billable_id = note.billable_id
JOIN
public.ja_mobiusers AS user_creator
ON
user_creator.id = note.mobiuserid
AND (
user_creator.name_first ilike 'Alan'
OR user_creator.name_last ilike 'Alan'
))SELECT
item.identity,
SUM(CASE WHEN item.for_invoicing THEN item.revenue ELSE 0 END) AS revenue,
SUM(CASE WHEN item.for_invoicing THEN item.quantity ELSE 0 END) AS quantity,
SUM(CASE WHEN item.for_invoicing THEN item.cost ELSE 0 END) AS costFROM
items AS itemGROUP BY
item.identityORDER BY
revenue DESC,
item.identity ASC

Explain analyze link: http://explain.depesz.com/s/IIDj

Re: [GENERAL] pg_upgrade 9.5.1: pg_upgrade_support missing

[Adrian Klaver]

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
Files\PostgreSQL\9.5\bin" -
d "C:\Program Files\PostgreSQL\9.4\data" -D "C:\Program
Files\PostgreSQL\9.5\data" -p 5433 -P 5432 -U postgres
Performing Consistency Checks
-
Checking cluster versions   ok
Checking database user is the install user  ok
Checking database connection settings   ok
Checking for prepared transactions  ok
Checking for reg* system OID user data typesok
Checking for contrib/isn with bigint-passing mismatch   ok
Creating dump of global objects ok
Creating dump of database schemas
 ok
Checking for presence of required libraries fatal
Your installation references loadable libraries that are missing
from the
new installation.  You can add these libraries to the new installation,
or remove the functions using them from the old installation.  A list of
problem libraries is in the file:
 loadable_libraries.txt
Failure, exiting


The file  loadable_libraries.txt  contains:


Could not load library "$libdir/pg_upgrade_support"
FEHLER: konnte nicht auf Datei „$libdir/pg_upgrade_support“
zugreifen: No such file or directory



I tried to copy the dll from the 9.4 version but the interface seems not
to be compatible.

I use postgres under Windows 7 64 Bit.


So how did you install Postgres(and from where) and was it different for 
9.4 versus 9.5?




Note: This is a question first asked here:
https://www.pg-forum.de/viewtopic.php?f=54=7495

Thanks,
Tobias



--
Adrian Klaver
adrian.kla...@aklaver.com


--
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] RLS on catalog tables would be helpful

[Julien Rouhaud]

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:
>>
>>> 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 functionality you would require to that thread.
>>
>> And how would one do that? (Not trying to be difficult, I really have no
>> idea how to join a thread that I have no email archive for).
> 
> 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
https://lists.postgresql.org/mj/mj_wwwusr?list=pgsql-hackers=on=archive-get-part=201602/753

once you log in you'll find the "Mail this message to..." link at bottom
of the page.

> However, if you view the raw message (there is a link for that on the
> archives), save it locally, and then open it in your email client, you
> can then hit "reply-all".
> 
> HTH,
> 
> Joe
> 


-- 
Julien Rouhaud
http://dalibo.com - http://dalibo.org


-- 
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] RLS on catalog tables would be helpful

[Pavel Stehule]

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:
> >
> >>
> 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 functionality you would require to that thread.
> >
> > And how would one do that? (Not trying to be difficult, I really have no
> > idea how to join a thread that I have no email archive for).
>
> 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 :-/
>

https://lists.postgresql.org/mj/mj_wwwusr/domain=postgresql.org

Sign, go to archive, and there are link "Mail this message

"

Regards

Pavel


>
> However, if you view the raw message (there is a link for that on the
> archives), save it locally, and then open it in your email client, you
> can then hit "reply-all".
>
> HTH,
>
> Joe
>
> --
> Crunchy Data - http://crunchydata.com
> PostgreSQL Support for Secure Enterprises
> Consulting, Training, & Open Source Development
>
>

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

[Adrian Klaver]

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:


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 functionality you would require to that thread.


And how would one do that? (Not trying to be difficult, I really have no
idea how to join a thread that I have no email archive for).


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


If you go to a specific message in the archive there is a 'Mail this 
message' link at the bottom of the message that will mail to the user. I 
just tried it and it said it sent the message, though I have not 
received it.




However, if you view the raw message (there is a link for that on the
archives), save it locally, and then open it in your email client, you
can then hit "reply-all".

HTH,

Joe




--
Adrian Klaver
adrian.kla...@aklaver.com


--
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] RLS on catalog tables would be helpful

[Joe Conway]

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 for you to add your thoughts on your use case and
>> specific functionality you would require to that thread.
> 
> And how would one do that? (Not trying to be difficult, I really have no
> idea how to join a thread that I have no email archive for).

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

However, if you view the raw message (there is a link for that on the
archives), save it locally, and then open it in your email client, you
can then hit "reply-all".

HTH,

Joe

-- 
Crunchy Data - http://crunchydata.com
PostgreSQL Support for Secure Enterprises
Consulting, Training, & Open Source Development



signature.asc
Description: OpenPGP digital signature

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

[Joshua D. Drake]

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 functionality you would require to that thread.

Joe



And how would one do that? (Not trying to be difficult, I really have no 
idea how to join a thread that I have no email archive for).


JD

--
Command Prompt, Inc.  http://the.postgres.company/
+1-503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Everyone appreciates your honesty, until you are honest with them.


--
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] Export binary data - PostgreSQL 9.2

[drum.lu...@gmail.com]

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, 2016 at 9:12 AM, drum.lu...@gmail.com <
> drum.lu...@gmail.com> wrote:
>
>> Hi all,
>>
>>
>> Which command would be to export the binary data for a table?
>>
>> I was unable to find it...
>>
>> Thanks
>>
>
>

Have just used the COPY  WITH Binary

Thank you.

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

[Joe Conway]

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 done to the system catalog by
> allowing users to mess with them, however  RLS seems to be a great
> idea in this case.
> 
> Has this been thought about?  Any plans to change in the future?

It has been discussed at some length and there is a specific
implementation patch that has been proposed. See:

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 functionality you would require to that thread.

Joe

-- 
Crunchy Data - http://crunchydata.com
PostgreSQL Support for Secure Enterprises
Consulting, Training, & Open Source Development



signature.asc
Description: OpenPGP digital signature

[GENERAL] RLS on catalog tables would be helpful

[Alan Droege]

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 mess with them, 
however  RLS

seems to be a great idea in this case.

Has this been thought about?  Any plans to change in the future?   I 
know that
we could write certain functions in "c", and that their implementation 
source would
be hiddenhowever that is not an option yet.  We would desire the 
PL/pgSQL code

of certain functions to be hidden ideally using RLS.

Thanks.



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

[Adrian Klaver]

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 subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL]

[Alan Droege]

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?

[Adrian Klaver]

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 '($user|foo|bar|public)' ...
 pg_dump -U myuser -n '("$user"|foo|bar|public)' ...
 pg_dump -U myuser -n '(\$user|foo|bar|public)' ...

I can't tell if I'm doing something wrong or if $user expansion is just
some magic in SET that doesn't exist in pg_dump or `\dn`.

(The workaround is obvious, of course: replace $user with the value of
the -U argument . This is a question of curiosity, not practicality.)

Also, is there any difference between `pg_dump -n '(foo|bar)'` and
`pg_dump -n foo -n bar`? In my narrow testing, they produce identical
results.


Not as far I can see:

http://www.postgresql.org/docs/9.5/interactive/app-pgdump.html#PG-DUMP-EXAMPLES

To dump all schemas whose names start with east or west and end in gsm, 
excluding any schemas whose names contain the word test:


$ pg_dump -n 'east*gsm' -n 'west*gsm' -N '*test*' mydb > db.sql

The same, using regular expression notation to consolidate the switches:

$ pg_dump -n '(east|west)*gsm' -N '*test*' mydb > db.sql



Thanks,

--
Mark E. Haase



--
Adrian Klaver
adrian.kla...@aklaver.com


--
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] How to ensure that a stored function always returns TRUE or FALSE?

[Pavel Stehule]

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
> 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 latter I would have to be careful with the way I call my
> function -
> and I am worried I might forget it later and this is a security related...
>
> So I will probably use this function:
>
> CREATE OR REPLACE FUNCTION check_user(in_social integer,
> in_sid varchar(255),
> in_auth varchar(32))
> RETURNS boolean AS
> $func$
> SELECT CASE
> WHEN in_social IS NULL THEN FALSE
> WHEN in_sidIS NULL THEN FALSE
> WHEN in_auth   IS NULL THEN FALSE
> ELSE (MD5('secret word' || in_social || in_sid) = in_auth)
> END;
>
> $func$ LANGUAGE sql IMMUTABLE;
>

this solution is ilustrative, but probably slower

I hope so function

REATE OR REPLACE FUNCTION check_user(in_social integer,
in_sid varchar(255),
in_auth varchar(32))
RETURNS boolean AS
$func$
SELECT COALESCE(MD5('secret word' || in_social || in_sid) =
in_auth, FALSE)
$func$ LANGUAGE sql IMMUTABLE;

should to return same result quckly.

Regards

Pavel



>
> Regards
> Alex
>
>
>
>
>
>

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

[Alexander Farber]

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 latter I would have to be careful with the way I call my
function -
and I am worried I might forget it later and this is a security related...

So I will probably use this function:

CREATE OR REPLACE FUNCTION check_user(in_social integer,
in_sid varchar(255),
in_auth varchar(32))
RETURNS boolean AS
$func$
SELECT CASE
WHEN in_social IS NULL THEN FALSE
WHEN in_sidIS NULL THEN FALSE
WHEN in_auth   IS NULL THEN FALSE
ELSE (MD5('secret word' || in_social || in_sid) = in_auth)
END;

$func$ LANGUAGE sql IMMUTABLE;

Regards
Alex

[GENERAL] $user namespace with pg_dump?

[Mark E. Haase]

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 '("$user"|foo|bar|public)' ...
pg_dump -U myuser -n '(\$user|foo|bar|public)' ...

I can't tell if I'm doing something wrong or if $user expansion is just
some magic in SET that doesn't exist in pg_dump or `\dn`.

(The workaround is obvious, of course: replace $user with the value of the
-U argument . This is a question of curiosity, not practicality.)

Also, is there any difference between `pg_dump -n '(foo|bar)'` and `pg_dump
-n foo -n bar`? In my narrow testing, they produce identical results.

Thanks,

-- 
Mark E. Haase

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

[Rémi Cura]

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 find_useless_postgres_file('your_database_name')`

This will output a list of files that are on the disk but not used by
postgres,
and so can be removed.

To be extra sure, you should use oid2name programme to check that the
useless files are really useless.


For this :
 * output the list of potential useless files with copy for instance
  ex :
  COPY ( SELECT file_name
 FROM find_useless_postgres_file('your_database_name')
   ) TO 'path_to_you_database_folder/potential_useless.txt'

   now you've got a file with a list of potential erroneous files.

 * Then use oid2name
  `$su postgres
   $cd path_to_you_database_folder
   $while read i; do oid2name -f "$i" -i -S -q -d your_database_name; done
< potential_useless.txt
  `

  Nothing should show, meaning that every potential erroneous file
   has not been recognized by oid2name !
  If you feel unconvinced, you can manually try oid2name on some
   of the potential erroneous files, to be extra sure.
  It should not find anything.

 * Now delete all the files in `potential_useless.txt`.
  It could be wiser to not delete the files but rename those
  (for instance, adding `.potentially_useless` as a postfix)
  so if it breaks something, you have an easy way to revert everything.

Anyway, use *-*extra extra*-* caution if you delete.
Except a backup, there would be no easy way to correct a mistake.

Cheers,
Rémi-C

2016-03-02 15:38 GMT+01:00 Johnny Morano :

> 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 cleanup of
> bloated data? (like in an example with commands and example output, if
> possible of course)
>
>
>
> Thanks!
>
>
>
>
>
> Mit besten Grüßen / With best regards,
>
> Johnny Morano
>
> 
>
>
>
> *Johnny Morano  | Principal Systems Engineer*
>
>
>
> PAY.ON GmbH  |  AN ACI WORLDWIDE COMPANY  |  WWW.PAYON.COM
> 
>
> Jakob-Haringer-Str. 1  |  5020 Salzburg  |  Austria
>
> Registered at: LG Salzburg  |  Company number: FN 315081 f  |  VAT-ID:
> ATU64439405
>
> Managing Director: Christian Bamberger
>
>
>
>
>
> Follow us on:
>
>
>
> [image: cid:image001.jpg@01D126D0.E1AB0670]   [image:
> cid:image002.jpg@01D126D0.E1AB0670]
>   [image:
> cid:image003.jpg@01D126D0.E1AB0670] 
>
>
>
> This email message and any attachments may contain confidential,
> proprietary or non-public information. This information is intended solely
> for the designated recipient(s). If an addressing or transmission error has
> misdirected this email, please notify the sender immediately and destroy
> this email. Any review, dissemination, use or reliance upon this
> information by unintended recipients is prohibited. Any opinions expressed
> in this email are those of the author personally.
>
>
>
> This message and any attachments have been scanned for viruses prior
> leaving PAY.ON; however, PAY.ON does not guarantee the security of this
> message and will not be responsible for any damages arising as a result of
> any virus being passed on or arising from any alteration of this message by
> a third party. PAY.ON may monitor e-mails sent to and from PAY.ON.
>
>
>
>
>
>
>
>
>
> *From:* pgsql-general-ow...@postgresql.org [mailto:
> pgsql-general-ow...@postgresql.org] *On Behalf Of *Rémi Cura
> *Sent:* Mittwoch, 2. März 2016 14:58
> *To:* Alvaro Herrera
> *Cc:* PostgreSQL General
> *Subject:* 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 folder.
> > I had about 8 Go of useless files.
>
> Would you add a new page to the wiki with this?
>
> https://wiki.postgresql.org/wiki/Category:Administrative_Snippets
>
> --
> Álvaro Herrerahttp://www.2ndQuadrant.com/
> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>
>
>

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

[David G. Johnston]

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 using it elsewhere...
>
>
​Merlin's point about inlining and SQL language functions not withstanding:​

You should consider writing a variation of the check_user function that
returns VOID or raises an exception and use is like an assertion.

​I would consider raising an exception if in_auth is NULL as I'd
potentially consider such a situation to represent mis-usage of the
function which should gets it own error instead of simply indicating that
the validation failed.
​
​David J.​

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

[David Bennett]

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 2/29/2016 3:55 PM, da...@andl.org   wrote:

What I need (to find or create) is a ‘pure’ C language API to support a 
Postgres server extension. By ‘pure’ I mean one that has no knowledge of 
Postgres internals and that could be called by a generic interface provided by 
some other tool that can support C language APIs.

 

The reason is that I’m looking to integrate a new language (Andl) into 
Postgres. To do that I need to find or create a ‘pure’ C-language API to 
interface with:

1.   The generic language call interface (pl_language_handler), which must 
handle conversion of Incoming language call parameters and outgoing language 
call return value.

1.   The backend query execution interface (SPI), which must handle 
conversion of outgoing query parameters and incoming query result values.



by "server extension" do you mean, you want to use Andi as a "PL/Andi" so you 
can write stored procedures i Andi callable within SQL queries?

 

Yes, more or less. The end result will probably be a little different for 
reasons that don’t matter here, but that would be a possible outcome.

 

I’ve gone through all the existing PL/xxx implementations and they all finish 
up with 10K lines of laborious C code. That’s 6 months’ work! I know I can 
write the code much faster in C++ or C#, but many of the access routines are 
exposed only as ‘impure’ C code such as macros, #defines, global variables, 
etc. I want to write a minimum layer of C code, exposing a ‘pure’ C API, and do 
most of the work (eg conversions), in a higher level language.

 

Sorry if I’m not explaining it well, but it’s not an easy thing to get across.

or do you mean, you want your Andi programs to be able to execute normal 
postgresql queries as a regular client ?  

 

No, not this. Server-side only.


for the first, see
http://www.postgresql.org/docs/9.5/static/plhandler.html

 

I know it well. I have a working sample [but it keeps crashing. The rules are 
not easy to work out and the docs are thin.]

 

I was hoping to turn up some project or sample that would get me further along, 
and faster.


for the latter, see
http://www.postgresql.org/docs/9.5/static/libpq.html






-- 
john r pierce, recycling bits in santa cruz

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

[Evgeny Morozov]

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 array_test_bit
> > JOIN generate_series(1, 1) n ON true;
>
> Substring on a bit string is not optimized for long TOASTed values.
> Substring on text is optimized for that. The current code fetches the whole
> 8MB from the table every time.
>
I see, thanks. Is there a better way to pack a large number of integers
efficiently with reasonable read/write performance?

I tried arrays bit varying, which seemed perfect, but in practice when I
stored 4M integers in it, each one taking as few bits as possible, the
table takes 13MB - same as if I just store all of them as bit(24). In fact,
an array of 4M bit(10) integers also takes 13MB. bit(8) takes only 0.7 MB.
bit(9) is where things get weird: for integer 1 to 4M it takes 13MB, but if
I multiple them by 2 (i.e. store 4M even integers) it takes 0.7MB! So there
must be some kind of compression going on there, but I don't understand how
it works.

[GENERAL] pg_upgrade 9.5.1: pg_upgrade_support missing

[schoetbi schoetbi]

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" -D "C:\Program
> Files\PostgreSQL\9.5\data" -p 5433 -P 5432 -U postgres
> Performing Consistency Checks
> -
> Checking cluster versions   ok
> Checking database user is the install user  ok
> Checking database connection settings   ok
> Checking for prepared transactions  ok
> Checking for reg* system OID user data typesok
> Checking for contrib/isn with bigint-passing mismatch   ok
> Creating dump of global objects ok
> Creating dump of database schemas
> ok
> Checking for presence of required libraries fatal
> Your installation references loadable libraries that are missing from the
> new installation.  You can add these libraries to the new installation,
> or remove the functions using them from the old installation.  A list of
> problem libraries is in the file:
> loadable_libraries.txt
> Failure, exiting


The file  loadable_libraries.txt  contains:
>
>
> Could not load library "$libdir/pg_upgrade_support"
> FEHLER: konnte nicht auf Datei „$libdir/pg_upgrade_support“ zugreifen: No
> such file or directory



I tried to copy the dll from the 9.4 version but the interface seems not to
be compatible.

I use postgres under Windows 7 64 Bit.

Note: This is a question first asked here:
https://www.pg-forum.de/viewtopic.php?f=54=7495

Thanks,
Tobias

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

[David Bennett]

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: [GENERAL] Looking for pure C function APIs for server extension: 
language handler and SPI

 

On Mon, Feb 29, 2016 at 5:55 PM,  > 
wrote:

What I need (to find or create) is a ‘pure’ C language API to support a 
Postgres server extension. By ‘pure’ I mean one that has no knowledge of 
Postgres internals and that could be called by a generic interface provided by 
some other tool that can support C language APIs.

 

​Well, since nobody else has replied yet, have you read: 
http://www.postgresql.org/docs/9.5/interactive/libpq.html

 

Thanks for noticing! Yes, but this is a server extension and libpq is client 
side. I want to use the generic language call handler and SPI query interface 
on the server, not client side.

 

libpq is the C callable API which communicates with the PostgreSQL server. 
There is a "shared object" and a normal library which can be "statically 
linked". But that's really all that _I_ know about it.

 

[dmb>] 

​ 

 

The reason is that I’m looking to integrate a new language (Andl) into 
Postgres. To do that I need to find or create a ‘pure’ C-language API to 
interface with:

1.   The generic language call interface (pl_language_handler), which must 
handle conversion of Incoming language call parameters and outgoing language 
call return value.

1.   The backend query execution interface (SPI), which must handle 
conversion of outgoing query parameters and incoming query result values.

 

There are 5 generic data types: boolean, binary (blob), number 
(decimal/real/integer), text (string/varchar), time (date/datetime). Each data 
type needs to be converted between the Postgres internal data types (Datum) and 
some intermediate data type that can be passed through a pure C API. In my case 
that will be C# (MS or Mono), but others might want to use Java or whatever.

 

These conversion tasks are identical to those needed to implement an ODBC/JDBC 
or similar interface, but one bound directly to the server and not going 
through a client (socket) connection. I have already done this successfully 
with Sqlite, which already provides a pure C server API and excellent 
documentation to go with it, so I know what it might look like.

 

FYI apart from these two APIs (and their 4x5 conversions), the only other thing 
needed is some SQL code generation and I expect to have a working language of 
considerable power.

 

Any help, suggestions, pointers much appreciated.

 

Regards

David M Bennett FACS


  _  


Andl - A New Database Language - andl.org  

 

 

-- 

The man has the intellect of a lobotomized turtle.


Maranatha! <><
John McKown

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

[Evgeny Morozov]

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 array_test_bit
> > JOIN generate_series(1, 1) n ON true;
>
> Substring on a bit string is not optimized for long TOASTed values.
> Substring on text is optimized for that. The current code fetches the whole
> 8MB from the table every time.
>
I see, thanks. Is there a better way to pack a large number of integers
efficiently with reasonable read/write performance?

I tried arrays bit varying, which seemed perfect, but in practice when I
stored 4M integers in it, each one taking as few bits as possible, the
table takes 13MB - same as if I just store all of them as bit(24). In fact,
an array of 4M bit(10) integers also takes 13MB. bit(8) takes only 0.7 MB.
bit(9) is where things get weird: for integer 1 to 4M it takes 13MB, but if
I multiple them by 2 (i.e. store 4M even integers) it takes 0.7MB! So there
must be some kind of compression going on there, but I don't understand how
it works.

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

[Vitaly Burovoy]

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(
>> > in_social integer,
>> > in_sid varchar(255),
>> > in_auth varchar(32))
>> > RETURNS boolean AS
>> > $func$
>> > SELECT MD5('secret word' || in_social || in_sid) =
>> in_auth;
>> > $func$ LANGUAGE sql IMMUTABLE;
>> >
>> >
>> > CREATE OR REPLACE FUNCTION test3() RETURNS void AS
>> > $func$
>> > BEGIN
>> > IF NOT check_user(42, 'user1', NULL) THEN
>> > RAISE NOTICE 'invalid user';
>> > ELSE
>> > RAISE NOTICE 'valid user';
>> > END IF;
>> > END
>> > $func$ LANGUAGE plpgsql;
>> >
>> > The 3rd function does NOT work as expected and prints "valid user".
>> >
>> > This happens because check_user() returns NULL instead of a boolean
>> value.
>>
>> I guess it is enough to swap blocks inside of IF statement and reverse
>> its condition:
>>
>> CREATE OR REPLACE FUNCTION test3() RETURNS void AS
>> $func$
>> BEGIN
>> IF check_user(42, 'user1', NULL) THEN
>> RAISE NOTICE 'valid user';
>> ELSE
>> RAISE NOTICE 'invalid user';
>> END IF;
>> END
>> $func$ LANGUAGE plpgsql;
>>
>> would give "invalid user". NULL works as FALSE at the top of IF
>> expressions.
>>
>>
>> [1]https://en.wikipedia.org/wiki/Null_(SQL)#Comparisons_with_NULL_and_the_three-valued_logic_.283VL.29
>
> 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...

As Merlin Moncure mentioned[2] the best way is to replace "=" by "IS
NOT DISTINCT FROM" in the "check_user" function.
But if you want to change only IF statement in "testX" functions it is
enough to replace the condition "IF NOT check_user(42, 'user1', NULL)
THEN" by "IF check_user(42, 'user1', NULL) IS NOT TRUE THEN". See the
example below:

postgres=# SELECT var, var IS NOT TRUE AS result
postgres-# FROM unnest(ARRAY[TRUE, FALSE, NULL]::bool[])as var;
 var | result
-+
 t   | f
 f   | t
 | t
(3 rows)

P.S.: please, don't top post.

[2]http://www.postgresql.org/message-id/cahyxu0xdfq--0atm3md7d1x5znfbjde0emjhnvtczdbjufd...@mail.gmail.com
-- 
Best regards,
Vitaly Burovoy


-- 
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] How to ensure that a stored function always returns TRUE or FALSE?

[Merlin Moncure]

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 varchar(255),
> in_auth varchar(32))
> RETURNS boolean AS
> $func$
> SELECT MD5('secret word' || in_social || in_sid) = in_auth;
> $func$ LANGUAGE sql IMMUTABLE;
>
> I am going to call it while looping through a JSON array of objects in
> another stored functions - and will RAISE EXCEPTION if it returns FALSE for
> any of the JSON objects (and thus rollback the whole transaction).

Personally I would write the check like this:
SELECT MD5('secret word' || in_social || in_sid) IS NOT DISTINCT FROM in_auth;

...for better handling of NULLS within the input arguments.  It is
definitely write for this function to be sql, not plpgsql, because it
is a good candidate for inlining.

Also, I tend to wrap RAISE NOTICE with a function:

CREATE OR REPLACE FUNCTION Notice(TEXT) RETURNS VOID AS
$$
BEGIN
  RAISE NOTICE '%', $1;
END;
$$ LANGUAGE PLPGSQL;

CREATE OR REPLACE FUNCTION Exception(TEXT) RETURNS VOID AS
$$
BEGIN
  RAISE NOTICE '%', $1;
END;
$$ LANGUAGE PLPGSQL;

CREATE OR REPLACE FUNCTION NoticeValue(anyelement) RETURNS anyelement AS
$$
  SELECT $1 FROM (SELECT NOTICE($1::TEXT)) q;
$$ LANGUAGE SQL IMMUTABLE;

Then you can write a checker function like this:

CREATE OR REPLACE FUNCTION test4() RETURNS void AS
$func$
BEGIN
PERFORM Exception('invalid user') WHERE NOT
check_user(42, 'user1', NULL);
END
$func$ LANGUAGE plpgsql;

"NoticeValue()" Is a wonderful debugging tool for pl/pgsql.  It allows
you to quickly virtually anything in a query without rewriting the
entire query.

SELECT NoticeValue(foo) FROM bar;

merlin


-- 
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] bloated postgres data folder, clean up

[Johnny Morano]

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 cleanup of 
bloated data? (like in an example with commands and example output, if possible 
of course)

Thanks!


Mit besten Grüßen / With best regards,
Johnny Morano


Johnny Morano  | Principal Systems Engineer

PAY.ON GmbH  |  AN ACI WORLDWIDE COMPANY  |  
WWW.PAYON.COM
Jakob-Haringer-Str. 1  |  5020 Salzburg  |  Austria
Registered at: LG Salzburg  |  Company number: FN 315081 f  |  VAT-ID: 
ATU64439405
Managing Director: Christian Bamberger


Follow us on:

[cid:image001.jpg@01D126D0.E1AB0670]  
[cid:image002.jpg@01D126D0.E1AB0670] 
   
[cid:image003.jpg@01D126D0.E1AB0670] 

This email message and any attachments may contain confidential, proprietary or 
non-public information. This information is intended solely for the designated 
recipient(s). If an addressing or transmission error has misdirected this 
email, please notify the sender immediately and destroy this email. Any review, 
dissemination, use or reliance upon this information by unintended recipients 
is prohibited. Any opinions expressed in this email are those of the author 
personally.

This message and any attachments have been scanned for viruses prior leaving 
PAY.ON; however, PAY.ON does not guarantee the security of this message and 
will not be responsible for any damages arising as a result of any virus being 
passed on or arising from any alteration of this message by a third party. 
PAY.ON may monitor e-mails sent to and from PAY.ON.




From: pgsql-general-ow...@postgresql.org 
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Rémi Cura
Sent: Mittwoch, 2. März 2016 14:58
To: Alvaro Herrera
Cc: PostgreSQL General
Subject: 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 folder.
> I had about 8 Go of useless files.

Would you add a new page to the wiki with this?

https://wiki.postgresql.org/wiki/Category:Administrative_Snippets

--
Álvaro Herrerahttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

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

[Rémi Cura]

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 folder.
> > I had about 8 Go of useless files.
>
> Would you add a new page to the wiki with this?
>
> https://wiki.postgresql.org/wiki/Category:Administrative_Snippets
>
> --
> Álvaro Herrerahttp://www.2ndQuadrant.com/
> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>

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

[Alexander Farber]

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

> On 3/2/16, Alexander Farber  wrote:
> >
> > CREATE OR REPLACE FUNCTION check_user(
> > in_social integer,
> > in_sid varchar(255),
> > in_auth varchar(32))
> > RETURNS boolean AS
> > $func$
> > SELECT MD5('secret word' || in_social || in_sid) =
> in_auth;
> > $func$ LANGUAGE sql IMMUTABLE;
> >
> >
> > CREATE OR REPLACE FUNCTION test3() RETURNS void AS
> > $func$
> > BEGIN
> > IF NOT check_user(42, 'user1', NULL) THEN
> > RAISE NOTICE 'invalid user';
> > ELSE
> > RAISE NOTICE 'valid user';
> > END IF;
> > END
> > $func$ LANGUAGE plpgsql;
> >
> > The 3rd function does NOT work as expected and prints "valid user".
> >
> > This happens because check_user() returns NULL instead of a boolean
> value.
>
> I guess it is enough to swap blocks inside of IF statement and reverse
> its condition:
>
> CREATE OR REPLACE FUNCTION test3() RETURNS void AS
> $func$
> BEGIN
> IF check_user(42, 'user1', NULL) THEN
> RAISE NOTICE 'valid user';
> ELSE
> RAISE NOTICE 'invalid user';
> END IF;
> END
> $func$ LANGUAGE plpgsql;
>
> would give "invalid user". NULL works as FALSE at the top of IF
> expressions.
>
>
> https://en.wikipedia.org/wiki/Null_(SQL)#Comparisons_with_NULL_and_the_three-valued_logic_.283VL.29
>
>

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

[fredrik]

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 to maintain high availability. We also run backups on the slave to reduce 
load on the master.

On both nodes, postgresqls data is stored on a SAN.

At some point, we restarted the slave. The first thing it does is to do a 
pg_basebackup and then start streaming changes based on that. This seemed to 
work fine, until 16 hrs later when we made a backup on the slave, using 
pg_dump. pg_dump was unable to complete, due to a pg_toast error (ERROR: 
missing chunk number 0 for toast value 2753291 in pg_toast_22066).

The issue remained until we ran a full vacuum analyze on the cluster.

We have been running in this way on several clusters for some years now, 
basically since 9.1 was released, without seeing this issue. Since we upgraded 
to postgresql 9.1.15, we have seen it twice. This does not necessarily mean 
anything, but I thought I would mention it.

The issue is resolved now, but I would still like to understand what happened.
I have logfiles from the incident, but I cannot see anything out of the 
ordinary (despite having a fair amount of experience investigating postgresql 
logs).

I have read that this kind of issues are most frequently due to hardware issues 
or bugs in postgresql.

Using ,
I have reviewed the release notes from the subsequent 9.1 releases 
(9.1.15-9.1.19),
but I have been unable to identify anything that hints at the issue that we 
have experienced.

the closest one would be:
(9.1.16,9.4.2,9.3.7,9.2.11,9.0.20) Fix possible failure during hash index 
bucket split, if other processes are modifying the index concurrently (Tom Lane)

but that seems like a long shot.

My question is:
- does anyone know of bug reports (fixed or otherwise) that could shed light on 
our issue?
- does anyone have experience with this kind of behavior?

- our setup is fairly automated; would it be a good idea to always run vacuum 
analyze after a pg_restore (or before pg_dump)?

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

[Albe Laurenz]

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 with a BEGIN ... EXCEPTION
>> block in PL/pgSQL, so you could write:
>> 
>> DECLARE FUNCTION  AS
>> $$BEGIN
>>/* UPDATE 1 */
>>UPDATE ...;
>>BEGIN  /* sets a savepoint */
>>   /* UPDATE 2, can cause an error */
>>   UPDATE ...;
>>EXCEPTION
>>   /* rollback to savepoint, ignore error */
>>   WHEN OTHERS THEN NULL;
>>END;
>> END;$$;
>> 
>> Even if UPDATE 2 throws an error, UPDATE 1 will be committed.

> Thank you, this is very helpful, just 1 little question:
> 
> 
> Why do you write just EXCEPTION?
> 
> 
> Shouldn't it be RAISE EXCEPTION?

That's something entirely different, see
http://www.postgresql.org/docs/current/static/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING

The above construct *catches* the exception, which might be
raised by the UPDATE statement.

Yours,
Laurenz Albe

-- 
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] Does RAISE EXCEPTION rollback previous commands in a stored function?

[Alexander Farber]

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 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, SAVEPOINT, COMMIT or ROLLBACK
> inside a function.  A function always runs within one transaction.
>
> Savepoints or subtransactions are written with a BEGIN ... EXCEPTION
> block in PL/pgSQL, so you could write:
>
> DECLARE FUNCTION  AS
> $$BEGIN
>/* UPDATE 1 */
>UPDATE ...;
>BEGIN  /* sets a savepoint */
>   /* UPDATE 2, can cause an error */
>   UPDATE ...;
>EXCEPTION
>   /* rollback to savepoint, ignore error */
>   WHEN OTHERS THEN NULL;
>END;
> END;$$;
>
> Even if UPDATE 2 throws an error, UPDATE 1 will be committed.
>
>

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

[david]

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: [GENERAL] Looking for pure C function APIs for server extension: 
language handler and SPI

 

On Mon, Feb 29, 2016 at 5:55 PM,  > 
wrote:

What I need (to find or create) is a ‘pure’ C language API to support a 
Postgres server extension. By ‘pure’ I mean one that has no knowledge of 
Postgres internals and that could be called by a generic interface provided by 
some other tool that can support C language APIs.

 

​Well, since nobody else has replied yet, have you read: 
http://www.postgresql.org/docs/9.5/interactive/libpq.html

 

Thanks for noticing! Yes, but this is a server extension and libpq is client 
side. I want to use the generic language call handler and SPI query interface 
on the server, not client side.

 

libpq is the C callable API which communicates with the PostgreSQL server. 
There is a "shared object" and a normal library which can be "statically 
linked". But that's really all that _I_ know about it.

 

[dmb>] 

​ 

 

The reason is that I’m looking to integrate a new language (Andl) into 
Postgres. To do that I need to find or create a ‘pure’ C-language API to 
interface with:

1.   The generic language call interface (pl_language_handler), which must 
handle conversion of Incoming language call parameters and outgoing language 
call return value.

1.   The backend query execution interface (SPI), which must handle 
conversion of outgoing query parameters and incoming query result values.

 

There are 5 generic data types: boolean, binary (blob), number 
(decimal/real/integer), text (string/varchar), time (date/datetime). Each data 
type needs to be converted between the Postgres internal data types (Datum) and 
some intermediate data type that can be passed through a pure C API. In my case 
that will be C# (MS or Mono), but others might want to use Java or whatever.

 

These conversion tasks are identical to those needed to implement an ODBC/JDBC 
or similar interface, but one bound directly to the server and not going 
through a client (socket) connection. I have already done this successfully 
with Sqlite, which already provides a pure C server API and excellent 
documentation to go with it, so I know what it might look like.

 

FYI apart from these two APIs (and their 4x5 conversions), the only other thing 
needed is some SQL code generation and I expect to have a working language of 
considerable power.

 

Any help, suggestions, pointers much appreciated.

 

Regards

David M Bennett FACS


  _  


Andl - A New Database Language - andl.org  

 

 

-- 

The man has the intellect of a lobotomized turtle.


Maranatha! <><
John McKown

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

[Albe Laurenz]

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, SAVEPOINT, COMMIT or ROLLBACK
inside a function.  A function always runs within one transaction.

Savepoints or subtransactions are written with a BEGIN ... EXCEPTION
block in PL/pgSQL, so you could write:

DECLARE FUNCTION  AS
$$BEGIN
   /* UPDATE 1 */
   UPDATE ...;
   BEGIN  /* sets a savepoint */
  /* UPDATE 2, can cause an error */
  UPDATE ...;
   EXCEPTION
  /* rollback to savepoint, ignore error */
  WHEN OTHERS THEN NULL;
   END;
END;$$;

Even if UPDATE 2 throws an error, UPDATE 1 will be committed.

Yours,
Laurenz Albe

-- 
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] Does RAISE EXCEPTION rollback previous commands in a stored function?

[Pavel Stehule]

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 explicitly. But, when you handle any exception in bloc,
then subtransaction is used implicitly

BEGIN ~ starts transaction
  ...
  ...
EXCEPTION  WHEN ... ~ rollback transaction

END -- commit transaction when no exception

http://www.postgresql.org/docs/9.1/static/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING

Regards

Pavel



>
> Regargs
> Alex
>
> On Wed, Mar 2, 2016 at 10:37 AM, Albe Laurenz 
> wrote:
>
>> Andreas Kretschmer wrote:
>> >> Alexander Farber  hat am 1. März 2016 um
>> 19:41
>> >> geschrieben:
>>
>
>
>> >> in PostgreSQL 9.5 does RAISE EXCEPTION reliably rollback all previous
>> >> commands in a stored function?
>> >
>> > Yes.
>>
>> That is, unless you set a savepoint to which you can rollback.
>>
>
>

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

[Vitaly Burovoy]

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),
> in_auth varchar(32))
> RETURNS boolean AS
> $func$
> SELECT MD5('secret word' || in_social || in_sid) = in_auth;
> $func$ LANGUAGE sql IMMUTABLE;
>
> I am going to call it while looping through a JSON array of objects in
> another stored functions - and will RAISE EXCEPTION if it returns FALSE for
> any of the JSON objects (and thus rollback the whole transaction).
>
> I have prepared 3 simple test functions below -
>
> 
>
> CREATE OR REPLACE FUNCTION test3() RETURNS void AS
> $func$
> BEGIN
> IF NOT check_user(42, 'user1', NULL) THEN
> RAISE NOTICE 'invalid user';
> ELSE
> RAISE NOTICE 'valid user';
> END IF;
> END
> $func$ LANGUAGE plpgsql;
>
> The 3rd function does NOT work as expected and prints "valid user".
>
> This happens because check_user() returns NULL instead of a boolean value.

I guess it is enough to swap blocks inside of IF statement and reverse
its condition:

CREATE OR REPLACE FUNCTION test3() RETURNS void AS
$func$
BEGIN
IF check_user(42, 'user1', NULL) THEN
RAISE NOTICE 'valid user';
ELSE
RAISE NOTICE 'invalid user';
END IF;
END
$func$ LANGUAGE plpgsql;

would give "invalid user". NULL works as FALSE at the top of IF expressions.
For more information see[1].

> COALESCE could be wrapped around the check_user() call in the
> IF-statement... but is there maybe a nicer way to solve this problem?
>
> Thank you
> Alex
>


[1]https://en.wikipedia.org/wiki/Null_(SQL)#Comparisons_with_NULL_and_the_three-valued_logic_.283VL.29
-- 
Best regards,
Vitaly Burovoy


-- 
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] Does RAISE EXCEPTION rollback previous commands in a stored function?

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

Regargs
Alex

On Wed, Mar 2, 2016 at 10:37 AM, Albe Laurenz 
wrote:

> Andreas Kretschmer wrote:
> >> Alexander Farber  hat am 1. März 2016 um
> 19:41
> >> geschrieben:
>


> >> in PostgreSQL 9.5 does RAISE EXCEPTION reliably rollback all previous
> >> commands in a stored function?
> >
> > Yes.
>
> That is, unless you set a savepoint to which you can rollback.
>

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

[Alexander Farber]

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$
SELECT MD5('secret word' || in_social || in_sid) = in_auth;
$func$ LANGUAGE sql IMMUTABLE;

I am going to call it while looping through a JSON array of objects in
another stored functions - and will RAISE EXCEPTION if it returns FALSE for
any of the JSON objects (and thus rollback the whole transaction).

I have prepared 3 simple test functions below -

CREATE OR REPLACE FUNCTION test1() RETURNS void AS
$func$
BEGIN
IF NOT check_user(42, 'user1',
'56db1046fa7b664c9b3d05bf7413552a') THEN
RAISE NOTICE 'invalid user';
ELSE
RAISE NOTICE 'valid user';
END IF;
END
$func$ LANGUAGE plpgsql;

The 1st function works as expected and prints "valid user".

CREATE OR REPLACE FUNCTION test2() RETURNS void AS
$func$
BEGIN
IF NOT check_user(42, 'user2',
'56db1046fa7b664c9b3d05bf7413552a') THEN
RAISE NOTICE 'invalid user';
ELSE
RAISE NOTICE 'valid user';
END IF;
END
$func$ LANGUAGE plpgsql;

The 2nd function works as expected and prints "invalid user".

CREATE OR REPLACE FUNCTION test3() RETURNS void AS
$func$
BEGIN
IF NOT check_user(42, 'user1', NULL) THEN
RAISE NOTICE 'invalid user';
ELSE
RAISE NOTICE 'valid user';
END IF;
END
$func$ LANGUAGE plpgsql;

The 3rd function does NOT work as expected and prints "valid user".

This happens because check_user() returns NULL instead of a boolean value.

COALESCE could be wrapped around the check_user() call in the
IF-statement... but is there maybe a nicer way to solve this problem?

Thank you
Alex

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

[Albe Laurenz]

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 savepoint to which you can rollback.

Yours,
Laurenz Albe

-- 
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] could not migrate 8.0.13 database with large object data to 9.5.1

[John R Pierce]

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 in pg_dump itself, so 
using a new pg_dump against the old server should be safe. 


yeah, following my direction, he's running pg_dump on the new 9.5 
server.   the old 8.0 database is on a different PC, an old XP system.




--
john r pierce, recycling bits in santa cruz



--
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] could not migrate 8.0.13 database with large object data to 9.5.1

[Magnus Hagander]

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 suggestion:
>> pg_dump -v -h 192.168.200.75 -U clubadmin -d clubprogram | psql -U
clubadmin -d clubprogram
>>
>> Result (got same message even with parameter -b or not):
>> pg_dump: reading rewrite rules
>> pg_dump: reading policies
>> pg_dump: reading large objects
>> ...
>>
>> pg_dump: creating FUNCTION "public.plpgsql_call_handler()"
>> pg_dump: creating FUNCTION "public.plpgsql_validator(oid)"
>> ...
>>
>> pg_dump: creating FUNCTION "public.pg_file_write(text, text, boolean)"
>
>
> those all sound like standard postgres functions, its not clear to me why
pg_dump is generating the CREATE FUNCTION code for them.
>
>
>> pg_dump: [archiver] could not write to output file: Invalid argument
>>
>
> ok, presumably your new box has plenty of disk space?  try this on the
new 9.5 system...
>

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 in pg_dump itself, so using a new pg_dump against the
old server should be safe.

/Magnus