Re: In which session context is a trigger run?

[Peter J. Holzer]

On 2018-12-30 08:56:13 -0800, Adrian Klaver wrote:
> On 12/30/18 3:08 AM, Peter J. Holzer wrote:
> > If I understood Mitar correctly he wants the trigger to execute in the
> > session where it was declared, not in the sessio where the statement was
> > executed that triggered the trigger.
> 
> There is the additional hitch that the trigger is being declared to use a
> temporary function that is defined in an alias schema pg_temp.

Yeah, but that would be neatly solved by the magic teleportation of the
trigger execution. (I think this is actually how he got the idea that
executing the trigger in a different session would solve his problem.)

hp

-- 
   _  | Peter J. Holzer| we build much bigger, better disasters now
|_|_) || because we have much more sophisticated
| |   | h...@hjp.at | management tools.
__/   | http://www.hjp.at/ | -- Ross Anderson 


signature.asc
Description: PGP signature

Re: In which session context is a trigger run?

[Mitar]

Hi!

On Sun, Dec 30, 2018 at 9:58 PM David G. Johnston
 wrote:
> Doesn’t seem likely.  Maybe you can use NOTIFY/LISTEN in your temporary 
> triggers and have your active client perform the work after being notified.

Yes, this is what I ended up doing. I signal the client and then the
client dispatches the call inside the correct client
connection/session. It seems to work fine. Probably latency it is
adding is also not too big a problem for me. I will see through time.


Mitar

-- 
http://mitar.tnode.com/
https://twitter.com/mitar_m

Re: In which session context is a trigger run?

[Mitar]

Hi!

On Mon, Dec 31, 2018 at 2:58 AM Peter J. Holzer  wrote:
> On 2018-12-30 08:56:13 -0800, Adrian Klaver wrote:
> > On 12/30/18 3:08 AM, Peter J. Holzer wrote:
> > > If I understood Mitar correctly he wants the trigger to execute in the
> > > session where it was declared, not in the sessio where the statement was
> > > executed that triggered the trigger.
> >
> > There is the additional hitch that the trigger is being declared to use a
> > temporary function that is defined in an alias schema pg_temp.
>
> Yeah, but that would be neatly solved by the magic teleportation of the
> trigger execution. (I think this is actually how he got the idea that
> executing the trigger in a different session would solve his problem.)

Thanks for understanding well what I am asking. Yes, it seems some
form of sending around triggers would be needed. I do not really care
if those triggers would run after transaction is committed. So they
should be some REALLY AFTER triggers. :-)

For now I am using NOTIFY/LISTEN to send information to the client
which then dispatches it to current connection/session. It allows me
also to batch multiple trigger calls together.


Mitar

-- 
http://mitar.tnode.com/
https://twitter.com/mitar_m

Re: libpq bug?

[Igor Korot]

Hi,
So, does anybody have an idea?

I do have following code at the beginning of the cpp file:

#ifdef WIN32
#include 
#pragma execution_character_set("utf-8")
#endif

but even running it on OSX, I am getting this same error.

Thank you.

On Fri, Dec 28, 2018 at 11:30 PM Igor Korot  wrote:
>
> Hi,
>
> On Sat, Dec 29, 2018 at 1:37 AM patrick keshishian  wrote:
> >
> > On Fri, Dec 28, 2018 at 5:40 PM Igor Korot  wrote:
> >>
> >> Hi, Patrick,
> >>
> >> Here is my new code:
> >>
> >> int PostgresDatabase::GetTableOwner (const std::wstring
> &schemaName,
> >> const std::wstring &tableName, std::wstring &owner,
> >> std::vector &errorMsg)
> >> {
> >> int result = 0;
> >> std::wstring query = L"SELECT u.usename FROM pg_class c, pg_user
> >> u, pg_namespace n WHERE n.oid = c.relnamespace AND u.usesysid =
> >> c.relowner AND n.nspname = $1 AND relname = $2";
> >> char *values[2];
> >> values[0] = NULL, values[1] = NULL;
> >> int charlength1 = schemaName.length() * sizeof( wchar_t ),
> >> charlength2 = tableName.length() * sizeof( wchar_t );
> >> values[0] = new char[schemaName.length() * sizeof( wchar_t ) + 
> 1];
> >> values[1] = new char[tableName.length() * sizeof( wchar_t ) + 1];
> >> memset( values[0], '\0', schemaName.length()  * sizeof(
> wchar_t ) + 1 );
> >> memset( values[1], '\0', tableName.length() * sizeof(
> wchar_t ) + 1 );
> >> strcpy( values[0], m_pimpl->m_myconv.to_bytes(
> schemaName.c_str()
> >> ).c_str() );
> >> strcpy( values[1], m_pimpl->m_myconv.to_bytes( 
> tableName.c_str()
> >> ).c_str() );
> >> int len1 = (int) schemaName.length() * sizeof( wchar_t );
> >> int len2 = (int) tableName.length() * sizeof( wchar_t );
> >> int length[2] = { len1, len2 };
> >> int formats[2] = { 1, 1 };
> >> PGresult *res = PQexecParams( m_db, 
> m_pimpl->m_myconv.to_bytes(
> >> query.c_str() ).c_str(), 2, NULL, values, length, formats, 1 );
> >> ExecStatusType status = PQresultStatus( res );
> >> if( status != PGRES_COMMAND_OK && status !=
> PGRES_TUPLES_OK )
> >> {
> >> result = 1;
> >> std::wstring err = m_pimpl->m_myconv.from_bytes(
> >> PQerrorMessage( m_db ) );
> >> errorMsg.push_back( L"Error executing query: " + err );
> >> PQclear( res );
> >> }
> >> else
> >> {
> >> owner = m_pimpl->m_myconv.from_bytes( PQgetvalue(
> res, 0, 0 ) );
> >> }
> >> return result;
> >> }
> >>
> >> The charlength2 variable contains the value of 8 and I'm still 
> getting
> >> the same error.
> >
> >
> > I was hoping someone more versed in C++ would jump in to answer
> your question. I haven't used C++ in at least a decade.
> > You need to convert the wchar_t  data that wstring stores into
> UTF-8. Personally, I would use iconv (common enough).
>
> But that is very weird.
> When I check what is stored in the values[1] array, I see the same
> byte sequence as what I got from the database
> information_schema.tables..
> Maybe I should just upgrade the libpq and try the latest release?
>
> >
> > I assume the PostgresDatabase class is your own (?) I would add a
> helper function to do the conversion. Here is a very rough template
> for you to adapt if you think it helps you.
>
> Yes, PostgresDatabase is my class.
> I will look at that later today, but it would definitely be
> interesting to get someone with the current C++
> experience (especially with C++11), because I believe that I am doing
> a conversion into UTF8.
>
> The m_convert variable is declared as:
>
> std::wstring_convert > m_myconv;
>
> and so I think it is converting to the UTF8.
>
> Thank you.
>
> >
> >
> > #include 
> >
> > #include 
> >
> > #include 
> >
> > #include 
> >
> > #include 
> >
> >
> > #include 
> >
> >
> > class PGDB {
> >
> > public:
> >
> > // your stuff ...
> >
> > iconv_t ic;
> >
> >
> > PGDB(void) {
> >
> > setlocale(LC_CTYPE, "");
> >
> > ic = iconv_open("UTF-8", "wchar_t");
> >
> > if ((iconv_t)-1 == ic)
> >
> > errx(1, "iconv_open");
> >
> > }
> >
> > ~PGDB() {
> >
> > iconv_close(ic);
> >
> > }
> >
> > // caller should free()
> >
> > char*wchar2utf8(std::wstring const &ws) {
> >
> > char*in, *buf, *out;
> >
> > size_t  bufsz, inbytes, outbytes;
> >
> >
> > in = (char *)ws.data();
> >
> > inbytes = ws.length() * sizeof(wchar_t);
> >
> > outbytes = inbytes;
> >
> > bufsz = inbytes + 1; // XXX check for overflow
> >
> >
> > buf = (char *)calloc(bufsz, 1);
> >
> > if (NULL == buf)
> >
> > err(1, NULL); // or throw something
> >
> >
> > out = buf;
> >
> > if ((size_t)-1 == iconv(ic, &in, &inbytes, &out, &outbytes))
> >
> > errx(1, "iconv"); /

Relocatable Binaries (RPMs) : custom installation path for PostgreSQL

[chiru r]

Hi All,

I wanted to install the PostgreSQL-11 Software with Custom
installation/binary paths.

Currently I am using EDB one click installer software with option file like
below to achieve, However EDB stopped and no longer provides one click
installer for Linux.

*Example:*

PostgreSQL install software file : *postgresql-9.6.9-1-linux-x64.run*

 *cat Optionsfile.txt*

mode=unattended

datadir=/u02/pgdata01/9.6/data

serverport=5432

prefix=/u01/postgres/9.6



I have installed Community  PostgreSQL RPMs and are going into "
/usr/pgsql-11/"
by default.


#-> pwd

/usr/pgsql-11

[root@user:/usr/pgsql-11]#

#-> ls -l

total 16

drwxr-xr-x 2 root root 4096 Dec 21 13:49 bin

drwxr-xr-x 3 root root 4096 Dec 21 13:49 doc

drwxr-xr-x 3 root root 4096 Dec 21 13:49 lib

drwxr-xr-x 8 root root 4096 Dec 21 13:49 share


Please let us know how to get the PostgreSQL-11 installed in above custom
paths using RPMs? .


Thanks,

Chiranjeevi

Thoughts on row-level security for webapps?

[Siegfried Bilstein]

Hi all,

I'm evaluating using a tool called Postgraphile that generates a GraphSQL
server from a postgres setup. The recommended way of handling security is
to implement RLS within postgres and simply have the webserver take a
cookie or similar and define which user is querying data.

I've normally built webapps like this: pull out user id from a session
cookie -> the API endpoint verifies the user and whether or not it has
access to the given data -> app code mutates the data.

With Postgraphile the request specifies the mutation and the server
processes the request and relies on Postgres to determine if the user has
correct access rights.

It seems like I would need to create a ROLE for every single member that
signs up for my website which I'm a little concerned about. Is this a
common usage pattern for SQL security? Any gotchas relying on RLS?

-- 
Siggy Bilstein
CTO of Ayuda Care 
Book some time  with me!

Re: getting pg_basebackup to use remote destination

[Jeff Janes]

On Sun, Dec 30, 2018 at 6:17 PM Chuck Martin 
wrote:

> Maybe I need to rethink ths and take Jeff's advice. I executed this:
>
> pg_basebackup -h [main server's URL] -U postgres -P -v -X s -D
> /mnt/dbraid/data
>
> 8 hours ago, and it is now still at 1%. Should it be that slow? The
> database in question is about 750 GB, and both servers are on the same GB
> ethernet network.
>

Over gigabit ethernet, it should not be that slow.  Unless the network is
saturated with other traffic or something.  Might be time to call in the
network engineers.  Can you transfer static files at high speeds between
those two hosts using scp or rsync?  (Or use some other technique to take
PostgreSQL out of the loop and see if your network is performing as it
should)

Are you seeing transfers at a constant slow rate, or are their long freezes
or something?  Maybe the initial checkpoint was extremely slow?
Unfortunately -P option (even with -v) doesn't make this easy to figure
out.  So alas it's back to old school stopwatch and a pen and paper (or
spreadsheet).

Cheers,

Jeff

>

Re: getting pg_basebackup to use remote destination

[Chuck Martin]

On Mon, Dec 31, 2018 at 12:05 PM Jeff Janes  wrote:

> On Sun, Dec 30, 2018 at 6:17 PM Chuck Martin 
> wrote:
>
>> Maybe I need to rethink ths and take Jeff's advice. I executed this:
>>
>> pg_basebackup -h [main server's URL] -U postgres -P -v -X s -D
>> /mnt/dbraid/data
>>
>> 8 hours ago, and it is now still at 1%. Should it be that slow? The
>> database in question is about 750 GB, and both servers are on the same GB
>> ethernet network.
>>
>
> Over gigabit ethernet, it should not be that slow.  Unless the network is
> saturated with other traffic or something.  Might be time to call in the
> network engineers.  Can you transfer static files at high speeds between
> those two hosts using scp or rsync?  (Or use some other technique to take
> PostgreSQL out of the loop and see if your network is performing as it
> should)
>
> Are you seeing transfers at a constant slow rate, or are their long
> freezes or something?  Maybe the initial checkpoint was extremely slow?
> Unfortunately -P option (even with -v) doesn't make this easy to figure
> out.  So alas it's back to old school stopwatch and a pen and paper (or
> spreadsheet).
>
> Cheers,
>
> Jeff
>
Using iperf, the transfer speed between the two servers (from the main to
the standby) was 938 Mbits/sec. If I understand the units correctly, it is
close to what it can be.

Your earlier suggestion was to do the pg_basebackup locally and rsync it
over. Maybe that would be faster. At this point, it is saying it is 6%
through, over 24 hours after being started.

Chuck Martin
Avondale Software

Can't quote_literal with COPY FROM PROGRAM

[Mark Mikulec]

Hi,

This command, which generates a JSON object as output, has some escaped
data with backslashes: (see line 91 here: https://pastebin.com/D4it8ybS)

C:\\Portable\\curl\\curl.exe -k "
https://maps.googleapis.com/maps/api/directions/json?etcVariablesDeleted";'

I use the COPY command to pull it into a temp table like so:

COPY temp_maps_api from program 'C:\\Portable\\curl\\curl.exe -k "
https://maps.googleapis.com/maps/api/directions/json?etcVariablesDeleted";';

However copy eats those backslashes. I need to use quote_literal() but
that's a syntax error. For some reason the COPY command doesn't allow for
ESCAPE to work with programs, only CSV.

I tried using WITH BINARY but I get the error message: "COPY file signature
not recognized"

Does anyone know how to make COPY FROM PROGRAM take the output literally?

Thanks,
  Mark
ᐧ

Re: Can't quote_literal with COPY FROM PROGRAM

[Rob Sargent]

> On Dec 31, 2018, at 10:36 AM, Mark Mikulec  wrote:
> 
> Hi,
> 
> This command, which generates a JSON object as output, has some escaped data 
> with backslashes: (see line 91 here: https://pastebin.com/D4it8ybS)
> 
> C:\\Portable\\curl\\curl.exe -k 
> "https://maps.googleapis.com/maps/api/directions/json?etcVariablesDeleted";'  
> 
> I use the COPY command to pull it into a temp table like so:
> 
> COPY temp_maps_api from program 'C:\\Portable\\curl\\curl.exe -k 
> "https://maps.googleapis.com/maps/api/directions/json?etcVariablesDeleted";';
> 
> However copy eats those backslashes. I need to use quote_literal() but that's 
> a syntax error. For some reason the COPY command doesn't allow for ESCAPE to 
> work with programs, only CSV.
> 
> I tried using WITH BINARY but I get the error message: "COPY file signature 
> not recognized"
> 
> Does anyone know how to make COPY FROM PROGRAM take the output literally?
> 
> Thanks,
>   Mark
> ᐧ
Can you pipe the curl output through sed s,\\,,g 

Re: Can't quote_literal with COPY FROM PROGRAM

[Mark Mikulec]

Thanks Rob,

Since I'm on Windows and Windows batch sucks I just ended up doing the JSON
parsing with node.js

To be honest this whole affair with COPY FROM program seems like a bug to
me though.

On Mon, Dec 31, 2018 at 1:59 PM Rob Sargent  wrote:

>
>
> On Dec 31, 2018, at 10:36 AM, Mark Mikulec  wrote:
>
> Hi,
>
> This command, which generates a JSON object as output, has some escaped
> data with backslashes: (see line 91 here: https://pastebin.com/D4it8ybS)
>
> C:\\Portable\\curl\\curl.exe -k "
> https://maps.googleapis.com/maps/api/directions/json?etcVariablesDeleted
> "'
>
> I use the COPY command to pull it into a temp table like so:
>
> COPY temp_maps_api from program 'C:\\Portable\\curl\\curl.exe -k "
> https://maps.googleapis.com/maps/api/directions/json?etcVariablesDeleted
> "';
>
> However copy eats those backslashes. I need to use quote_literal() but
> that's a syntax error. For some reason the COPY command doesn't allow for
> ESCAPE to work with programs, only CSV.
>
> I tried using WITH BINARY but I get the error message: "COPY file
> signature not recognized"
>
> Does anyone know how to make COPY FROM PROGRAM take the output literally?
>
> Thanks,
>   Mark
> ᐧ
>
> Can you pipe the curl output through sed s,\\,,g
>
> ᐧ

Re: Can't quote_literal with COPY FROM PROGRAM

[Adrian Klaver]

On 12/31/18 9:36 AM, Mark Mikulec wrote:

Hi,

This command, which generates a JSON object as output, has some escaped 
data with backslashes: (see line 91 here: https://pastebin.com/D4it8ybS)


C:\\Portable\\curl\\curl.exe -k 
"https://maps.googleapis.com/maps/api/directions/json?etcVariablesDeleted";'


I use the COPY command to pull it into a temp table like so:

COPY temp_maps_api from program 'C:\\Portable\\curl\\curl.exe -k 
"https://maps.googleapis.com/maps/api/directions/json?etcVariablesDeleted";';


So temp_maps_api has a single JSON field?



However copy eats those backslashes. I need to use quote_literal() but 
that's a syntax error. For some reason the COPY command doesn't allow 
for ESCAPE to work with programs, only CSV.


I tried using WITH BINARY but I get the error message: "COPY file 
signature not recognized"


Does anyone know how to make COPY FROM PROGRAM take the output literally?

Thanks,
   Mark
ᐧ



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

Re: Can't quote_literal with COPY FROM PROGRAM

[David G. Johnston]

On Monday, December 31, 2018, Mark Mikulec  wrote:
>
> Does anyone know how to make COPY FROM PROGRAM take the output literally?
>

Not that I can think of.  I’d avoid COPY FROM PROGRAM and move the logic to
psql. Roughly: \set varname `cmd`; select :’varname’;

David J.

Re: Can't quote_literal with COPY FROM PROGRAM

[Mark Mikulec]

I changed it to be just the single float value I needed to extract out of
the JSON object, but originally it was a text column that held the entire
JSON object.
ᐧ

On Mon, Dec 31, 2018 at 3:52 PM Adrian Klaver 
wrote:

> On 12/31/18 9:36 AM, Mark Mikulec wrote:
> > Hi,
> >
> > This command, which generates a JSON object as output, has some escaped
> > data with backslashes: (see line 91 here: https://pastebin.com/D4it8ybS)
> >
> > C:\\Portable\\curl\\curl.exe -k
> > "
> https://maps.googleapis.com/maps/api/directions/json?etcVariablesDeleted";'
> >
> > I use the COPY command to pull it into a temp table like so:
> >
> > COPY temp_maps_api from program 'C:\\Portable\\curl\\curl.exe -k
> > "
> https://maps.googleapis.com/maps/api/directions/json?etcVariablesDeleted
> "';
>
> So temp_maps_api has a single JSON field?
>
> >
> > However copy eats those backslashes. I need to use quote_literal() but
> > that's a syntax error. For some reason the COPY command doesn't allow
> > for ESCAPE to work with programs, only CSV.
> >
> > I tried using WITH BINARY but I get the error message: "COPY file
> > signature not recognized"
> >
> > Does anyone know how to make COPY FROM PROGRAM take the output literally?
> >
> > Thanks,
> >Mark
> > ᐧ
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

Re: Can't quote_literal with COPY FROM PROGRAM

[Adrian Klaver]

On 12/31/18 12:58 PM, Mark Mikulec wrote:
I changed it to be just the single float value I needed to extract out 
of the JSON object, but originally it was a text column that held the 
entire JSON object.


Might want to look at:

https://www.postgresql.org/docs/10/datatype-json.html

Might handle the escaping better.


ᐧ

On Mon, Dec 31, 2018 at 3:52 PM Adrian Klaver > wrote:


On 12/31/18 9:36 AM, Mark Mikulec wrote:
 > Hi,
 >
 > This command, which generates a JSON object as output, has some
escaped
 > data with backslashes: (see line 91 here:
https://pastebin.com/D4it8ybS)
 >
 > C:\\Portable\\curl\\curl.exe -k
 >
"https://maps.googleapis.com/maps/api/directions/json?etcVariablesDeleted";'
 >
 > I use the COPY command to pull it into a temp table like so:
 >
 > COPY temp_maps_api from program 'C:\\Portable\\curl\\curl.exe -k
 >
"https://maps.googleapis.com/maps/api/directions/json?etcVariablesDeleted";';

So temp_maps_api has a single JSON field?

 >
 > However copy eats those backslashes. I need to use
quote_literal() but
 > that's a syntax error. For some reason the COPY command doesn't
allow
 > for ESCAPE to work with programs, only CSV.
 >
 > I tried using WITH BINARY but I get the error message: "COPY file
 > signature not recognized"
 >
 > Does anyone know how to make COPY FROM PROGRAM take the output
literally?
 >
 > Thanks,
 >    Mark
 > ᐧ


-- 
Adrian Klaver

adrian.kla...@aklaver.com 




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

Re: Can't quote_literal with COPY FROM PROGRAM

[Andrew Gierth]

> "Mark" == Mark Mikulec  writes:

 Mark> To be honest this whole affair with COPY FROM program seems like
 Mark> a bug to me though.

Perhaps you're misunderstanding what COPY FROM PROGRAM is actually for.
Its purpose is to do exactly what COPY does, that is to say, take as
input a file in either PG's tab-delimited format or in CSV format, break
it into records and fields, and insert the data into a table. Note that
JSON is not a supported input format for COPY, though of course JSON
_values_ can appear as data within a field inside either the
tab-delimited or CSV formats. COPY FROM PROGRAM simply does COPY but
with the input (whether in tab or CSV format) taken from the output of
the program rather than a file.

In tab-delimited format, the delimiter can be changed to something other
than a tab, but the escape character is fixed as \ and the characters
NL, CR, \, and the delimiter character are required to be escaped. Thus,
any literal \ in the data MUST be escaped as \\ before passing the data
to COPY in this mode. In CSV mode, CSV quoting and escaping rules are
followed.

It's not COPY's job to read a single datum, whether in JSON format or
anything else.

-- 
Andrew (irc:RhodiumToad)