Re: Postgresql database encryption

[Igor Korot]

Hi, Vikas,

On Fri, Apr 20, 2018 at 10:24 AM, Vikas Sharma  wrote:
> Hello Guys,
>
> Could someone throw light on the postgresql instance wide or database wide
> encryption please? Is this possible in postgresql and been in use in
> production?.
>
> This is a requirement in our production implementation.

Yes, it is possible.
We have a PostgreSQL DB encrypted in our project.

I'm not sure what was used though - OS or DB implementation. We use RHEL6.

Thank you.

>
> Many Thanks
> Vikas Sharma

Add schema to the query

[Igor Korot]

Hi, ALL,

Is there an easy way to add the schema to the following query:

SELECT u.usename FROM pg_class c, pg_user u WHERE u.usesysid =
c.relowner AND relname = ?

Thank you.

Re: Add schema to the query

[Igor Korot]

Hi, Melvin,

On Sun, May 6, 2018 at 9:37 PM, Melvin Davidson  wrote:
>
>
> On Sun, May 6, 2018 at 10:33 PM, Melvin Davidson 
> wrote:
>>
>>
>>
>> On Sun, May 6, 2018 at 10:19 PM, Igor Korot  wrote:
>>>
>>> Hi, ALL,
>>>
>>> Is there an easy way to add the schema to the following query:
>>>
>>> SELECT u.usename FROM pg_class c, pg_user u WHERE u.usesysid =
>>> c.relowner AND relname = ?
>>>
>>> Thank you.
>>>
>>
>> >Is there an easy way to add the schema to the following query:
>>
>> You mean like this?
>> SELECT u.usename,
>>n.nspname AS schema
>>   FROM pg_class c
>> JOIN pg_namespace n ON n.oid = c.relnamespace,
>>pg_user u
>>  WHERE u.usesysid = c.relowner
>>AND relname = ?
>>
>>
>> --
>> Melvin Davidson
>> Maj. Database & Exploration Specialist
>> Universe Exploration Command – UXC
>> Employment by invitation only!
>
>
> OR do you mean like this?
> SELECT u.usename
>   FROM pg_class c
> JOIN pg_namespace n ON n.oid = c.relnamespace,
>pg_user u
>  WHERE u.usesysid = c.relowner
>AND relname = ?
>AND n.nspname = 'public'

Sorry needed to filter on the schema + table.

So, thank you.

>
>
> --
> Melvin Davidson
> Maj. Database & Exploration Specialist
> Universe Exploration Command – UXC
> Employment by invitation only!

Re: Add schema to the query

[Igor Korot]

Rob,

On Sun, May 6, 2018 at 11:54 PM, Rob Sargent  wrote:
>
>
>> On May 6, 2018, at 8:19 PM, Igor Korot  wrote:
>>
>> Hi, ALL,
>>
>> Is there an easy way to add the schema to the following query:
>>
>> SELECT u.usename FROM pg_class c, pg_user u WHERE u.usesysid =
>> c.relowner AND relname = ?
>>
>> Thank you.
>>
> Are you looking for the owner of a particular schema.tablename?

Yes, I am.

That;s what I ended up with:

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 = ?
AND relname = ?

Thank you.

Reset the cursor to start from the record 1

[Igor Korot]

 Hi, ALL,
I'd like to do the following:

[code]
PGresult res = PQprepare();
if( PQresultStatus( res ) != PGRES_COMMAND_OK )
{
// error handling
}
else
{
PGresult res1 = PQexecPrepared();
status = PQresultStatus( res1 );
if( status != PGRES_COMMAND_OK && status != PGRES_TUPLES_OK )
{
// error handling
}
else if( status == PGRES_TUPLES_OK )
{
for( int j = 0; j < PQntuples( res1 ); j++ )
{
// process the recordset
}
for( int j = 0; j < PQntuples( res1); j++ )
{
// process the same recordset again
}
}
}
[/code]

Is there a function which just reset the record pointer to 1, so I can reprocess
those records?

Thank you.

Is there a way to be notified on the CREATE TABLE execution?

[Igor Korot]

Hi, ALL,
Consider a scenario:

1. A software that uses libpq is executing.
2. Someone opens up a terminal and creates a table.
3. A software needs to know about this new table.

I presume this is a DBMS-specific...

Thank you.

Re: Is there a way to be notified on the CREATE TABLE execution?

[Igor Korot]

Hi, David,

On Tue, Jun 19, 2018 at 5:13 PM, David G. Johnston
 wrote:
> On Tuesday, June 19, 2018, Igor Korot  wrote:
>>
>> Hi, ALL,
>> Consider a scenario:
>>
>> 1. A software that uses libpq is executing.
>> 2. Someone opens up a terminal and creates a table.
>> 3. A software needs to know about this new table.
>
>
> I'd start here:
>
>  https://www.postgresql.org/docs/10/static/sql-createeventtrigger.html
>
> Your scenario suggests you may wish to avail yourself of the Listen and
> Notify commands as well.

I did look at the Listen/Notify.
Unfortunately the listening is done on the channel versus listening
for the specific event.

I also looked at the
https://www.postgresql.org/docs/9.1/static/libpq-example.html#LIBPQ-EXAMPLE-2,
but am not sure how to create an appropriate event.

Thank you.

>
> David J.
>
>
>

Re: Is there a way to be notified on the CREATE TABLE execution?

[Igor Korot]

Thx, Francisco.
It makes sense now.

Just one more question:

This page https://www.postgresql.org/docs/current/static/xfunc-c.html#DFUNC
does not reference
Windows/MSVC/MinGW as a way to compile the code.

How should I do it?

Thx.


On Wed, Jun 20, 2018 at 11:44 AM, Francisco Olarte
 wrote:
> On Wed, Jun 20, 2018 at 5:20 PM, Igor Korot  wrote:
>>>  https://www.postgresql.org/docs/10/static/sql-createeventtrigger.html
>>> Your scenario suggests you may wish to avail yourself of the Listen and
>>> Notify commands as well.
>>
>> I did look at the Listen/Notify.
>> Unfortunately the listening is done on the channel versus listening
>> for the specific event.
>
> Channels are cheap. You just listen on "whatever" and in the event
> trigger you notify "whatever", payload is optional.
>
> The event trigger is the one which takes care of filtering the event
> and notifying selectively.
>
> You can use a channel per event.
>
> Francisco Olarte.

Re: Is there a way to be notified on the CREATE TABLE execution?

[Igor Korot]

Hi, Francisco,

On Wed, Jun 20, 2018 at 12:22 PM, Francisco Olarte
 wrote:
> Igor:
>
> On Wed, Jun 20, 2018 at 7:07 PM, Igor Korot  wrote:
>> Just one more question:
>> This page https://www.postgresql.org/docs/current/static/xfunc-c.html#DFUNC
>> does not reference
>> Windows/MSVC/MinGW as a way to compile the code.
>
> Sorry, I don't do windows.
>
> You do not need C extension functions anyway, unless your usage
> pattern is truly bizarre a triger for ddl could be written in any pl.

>From the 
>https://www.postgresql.org/docs/current/static/event-trigger-definition.html:

[quote]
In order to create an event trigger, you must first create a function
with the special return type event_trigger. This function need not
(and may not) return a value; the return type serves merely as a
signal that the function is to be invoked as an event trigger.
[/quote]

So, the function has to be created and compiled.

Am I missing something?

Thank you.

>
> Francisco Olarte.

How to watch for schema changes

[Igor Korot]

Hi, ALL,
Is there any trigger or some other means I can do on the server
which will watch for CREATE/ALTER/DROP TABLE command and after successful
execution of those will issue a NOTIFY statement?

Thank you.

Re: How to watch for schema changes

[Igor Korot]

Adrian,

On Tue, Jul 3, 2018 at 12:32 PM, Adrian Klaver
 wrote:
> On 07/03/2018 10:21 AM, Igor Korot wrote:
>>
>> Hi, ALL,
>> Is there any trigger or some other means I can do on the server
>> which will watch for CREATE/ALTER/DROP TABLE command and after successful
>> execution of those will issue a NOTIFY statement?
>
>
> https://www.postgresql.org/docs/10/static/event-triggers.html

According to the documentation the lowest version it supports is 9.3.
Anything prior to that?

I'm working with OX 10.8 and it has 9.1 installed.

And a second question - how do I work with it?
I presume that function will have to be compiled in its own module
(either dll, so or dylib).
But then from the libpq interface how do I call it?

Thank you.

>
>>
>> Thank you.
>>
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com

Re: How to watch for schema changes

[Igor Korot]

Adrian,

On Tue, Jul 3, 2018 at 1:24 PM, Adrian Klaver  wrote:
> On 07/03/2018 11:15 AM, Igor Korot wrote:
>>
>> Adrian,
>>
>> On Tue, Jul 3, 2018 at 12:32 PM, Adrian Klaver
>>  wrote:
>>>
>>> On 07/03/2018 10:21 AM, Igor Korot wrote:
>>>>
>>>>
>>>> Hi, ALL,
>>>> Is there any trigger or some other means I can do on the server
>>>> which will watch for CREATE/ALTER/DROP TABLE command and after
>>>> successful
>>>> execution of those will issue a NOTIFY statement?
>>>
>>>
>>>
>>> https://www.postgresql.org/docs/10/static/event-triggers.html
>>
>>
>> According to the documentation the lowest version it supports is 9.3.
>> Anything prior to that?
>>
>> I'm working with OX 10.8 and it has 9.1 installed.
>
> 9.1 went EOL almost two years ago. The oldest supported version is 9.3,
> though it will go EOL this September:
>
> https://www.postgresql.org/support/versioning/
>
> Are you forced to work with 9.1 or can you use something from here:
>
> https://www.postgresql.org/download/macosx/
>
> to get a newer version? FYI that will be a major upgrade so will require a
> dump/restore or use of pg_upgrade.

Unfortunately I'm stuck with 9.1.
But I have a Linux machine which have a newer version so I can test
this solution.
And it would be nice to have both machine/versions working.

>
>>
>> And a second question - how do I work with it?
>> I presume that function will have to be compiled in its own module
>> (either dll, so or dylib).
>> But then from the libpq interface how do I call it?
>
>
> It can use functions written in PL languages. See below:
>
> https://www.postgresql.org/docs/10/static/sql-createeventtrigger.html
>
> for an example written in plpgsql.

OK.
I presume threre is a query which check for the function/trigger
existence? Something like:

IF NOT EXIST(SELECT * FROM ) CREATE OR REPLACE FUNCTION;

Thank you.

>
>
>
>
>
>>
>> Thank you.
>>
>>>
>>>>
>>>> Thank you.
>>>>
>>>
>>>
>>> --
>>> Adrian Klaver
>>> adrian.kla...@aklaver.com
>
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com

Re: How to watch for schema changes

[Igor Korot]

Hi Melvin



On Tue, Jul 3, 2018, 2:00 PM Melvin Davidson  wrote:

>
>
> >Unfortunately I'm stuck with 9.1.
>
> Have you thought about just setting   *log_statement = 'ddl'* in
> postgresql.conf
> and just greping the log for CREATE and ALTER?
>

That going to be not that simple.
I'm writing a client in C++ with libpq. So I will have to do a lot of
polling .

Thank you.


>

Re: How to watch for schema changes

[Igor Korot]

Hi, Melvin,

On Tue, Jul 3, 2018 at 6:48 PM, Melvin Davidson  wrote:
>
>>I'm writing a client in C++ with libpq. So I will have to do a lot of
>> polling .
> Can't you just run a cron job?

And what?
As I said I'm writing the client application with libpq/ODBC. How will I get
the results?

Thank you.

>
> --
> Melvin Davidson
> Maj. Database & Exploration Specialist
> Universe Exploration Command – UXC
> Employment by invitation only!

Re: How to watch for schema changes

[Igor Korot]

Hi, David,

On Tue, Jul 3, 2018 at 1:46 PM, David G. Johnston
 wrote:
> On Tue, Jul 3, 2018 at 11:41 AM, Igor Korot  wrote:
>>
>>
>> I presume threre is a query which check for the function/trigger
>> existence? Something like:
>>
>> IF NOT EXIST(SELECT * FROM ) CREATE OR REPLACE FUNCTION;
>
>
> CREATE OR REPLACE is how you re-create a function that (whose
> name/signature) might already exist; CREATE already assumes one doesn't
> exist.

Why do I need to re-create a function with exactly the same name and body?
Can't I just check if such function exists?

Thank you.

>
> David J.
>

Re: How to watch for schema changes

[Igor Korot]

David,

On Thu, Jul 5, 2018 at 11:49 AM, David G. Johnston
 wrote:
> On Thu, Jul 5, 2018 at 8:40 AM, Igor Korot  wrote:
>>
>> Hi, David,
>>
>> On Tue, Jul 3, 2018 at 1:46 PM, David G. Johnston
>>  wrote:
>> > On Tue, Jul 3, 2018 at 11:41 AM, Igor Korot  wrote:
>> >>
>> >>
>> >> I presume threre is a query which check for the function/trigger
>> >> existence? Something like:
>> >>
>> >> IF NOT EXIST(SELECT * FROM ) CREATE OR REPLACE FUNCTION;
>> >
>> >
>> > CREATE OR REPLACE is how you re-create a function that (whose
>> > name/signature) might already exist; CREATE already assumes one doesn't
>> > exist.
>>
>> Why do I need to re-create a function with exactly the same name and body?
>> Can't I just check if such function exists?
>
>
> You can, and depending on how often you intend to execute said code, it is
> probably the better way.  It also requires pl/pgsql while CREATE OR REPLACE
> "just works" as a single SQL command.  It seems easier to give you the
> simple answer than to work out the details for the more complex one.

Is it the same from the DB server POV? Meaning it is also the same 1/2
hit depending on the existence? Also performance-wise querying and this
method is the same, right?

Thank you.

>
> David J.
>

Re: How to watch for schema changes

[Igor Korot]

Hi, Adrian

On Tue, Jul 3, 2018 at 1:24 PM, Adrian Klaver  wrote:
> On 07/03/2018 11:15 AM, Igor Korot wrote:
>>
>> Adrian,
>>
>> On Tue, Jul 3, 2018 at 12:32 PM, Adrian Klaver
>>  wrote:
>>>
>>> On 07/03/2018 10:21 AM, Igor Korot wrote:
>>>>
>>>>
>>>> Hi, ALL,
>>>> Is there any trigger or some other means I can do on the server
>>>> which will watch for CREATE/ALTER/DROP TABLE command and after
>>>> successful
>>>> execution of those will issue a NOTIFY statement?
>>>
>>>
>>>
>>> https://www.postgresql.org/docs/10/static/event-triggers.html
>>
>>
>> According to the documentation the lowest version it supports is 9.3.
>> Anything prior to that?
>>
>> I'm working with OX 10.8 and it has 9.1 installed.
>
> 9.1 went EOL almost two years ago. The oldest supported version is 9.3,
> though it will go EOL this September:
>
> https://www.postgresql.org/support/versioning/
>
> Are you forced to work with 9.1 or can you use something from here:
>
> https://www.postgresql.org/download/macosx/
>
> to get a newer version? FYI that will be a major upgrade so will require a
> dump/restore or use of pg_upgrade.

Just a thought...
Is it possible to create a trigger for a system table? Or this
operation is restricted
for when the server is actually being set-up?

Successful "CREATE TABLE..." statement creates a row inside the
information_schema.tables
so if I can create a trigger after this record is inserted or deleted
that should be fine.

Thank you.

>
>>
>> And a second question - how do I work with it?
>> I presume that function will have to be compiled in its own module
>> (either dll, so or dylib).
>> But then from the libpq interface how do I call it?
>
>
> It can use functions written in PL languages. See below:
>
> https://www.postgresql.org/docs/10/static/sql-createeventtrigger.html
>
> for an example written in plpgsql.
>
>
>
>
>
>>
>> Thank you.
>>
>>>
>>>>
>>>> Thank you.
>>>>
>>>
>>>
>>> --
>>> Adrian Klaver
>>> adrian.kla...@aklaver.com
>
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com

Re: How to watch for schema changes

[Igor Korot]

Hi, guys,


On Mon, Jul 9, 2018 at 5:38 PM, Adrian Klaver  wrote:
> On 07/09/2018 01:49 PM, Igor Korot wrote:
>>
>> Hi, Adrian
>>
>> On Tue, Jul 3, 2018 at 1:24 PM, Adrian Klaver 
>> wrote:
>>>
>>> On 07/03/2018 11:15 AM, Igor Korot wrote:
>>>>
>>>>
>>>> Adrian,
>>>>
>>>> On Tue, Jul 3, 2018 at 12:32 PM, Adrian Klaver
>>>>  wrote:
>>>>>
>>>>>
>>>>> On 07/03/2018 10:21 AM, Igor Korot wrote:
>>>>>>
>>>>>>
>>>>>>
>>>>>> Hi, ALL,
>>>>>> Is there any trigger or some other means I can do on the server
>>>>>> which will watch for CREATE/ALTER/DROP TABLE command and after
>>>>>> successful
>>>>>> execution of those will issue a NOTIFY statement?
>>>>>
>>>>>
>>>>>
>>>>>
>>>>> https://www.postgresql.org/docs/10/static/event-triggers.html
>>>>
>>>>
>>>>
>>>> According to the documentation the lowest version it supports is 9.3.
>>>> Anything prior to that?
>>>>
>>>> I'm working with OX 10.8 and it has 9.1 installed.
>>>
>>>
>>> 9.1 went EOL almost two years ago. The oldest supported version is 9.3,
>>> though it will go EOL this September:
>>>
>>> https://www.postgresql.org/support/versioning/
>>>
>>> Are you forced to work with 9.1 or can you use something from here:
>>>
>>> https://www.postgresql.org/download/macosx/
>>>
>>> to get a newer version? FYI that will be a major upgrade so will require
>>> a
>>> dump/restore or use of pg_upgrade.
>>
>>
>> Just a thought...
>> Is it possible to create a trigger for a system table? Or this
>> operation is restricted
>
>
> Easy enough to test. As postgres super user:
>
> test_(postgres)# create trigger info_test before insert on pg_class execute
> procedure ts_update();
>
> ERROR:  permission denied: "pg_class" is a system catalog

But

draft=# CREATE TRIGGER info_test AFTER INSERT ON
information_schema.tables EXECUTE PROCEDURE test();
ERROR:  function test() does not exist

So it looks like this should be possible?

Thank you.

>
>
>> for when the server is actually being set-up?
>>
>> Successful "CREATE TABLE..." statement creates a row inside the
>> information_schema.tables
>> so if I can create a trigger after this record is inserted or deleted
>> that should be fine.
>>
>> Thank you.
>>
>>>
>>>>
>>>> And a second question - how do I work with it?
>>>> I presume that function will have to be compiled in its own module
>>>> (either dll, so or dylib).
>>>> But then from the libpq interface how do I call it?
>>>
>>>
>>>
>>> It can use functions written in PL languages. See below:
>>>
>>> https://www.postgresql.org/docs/10/static/sql-createeventtrigger.html
>>>
>>> for an example written in plpgsql.
>>>
>>>
>>>
>>>
>>>
>>>>
>>>> Thank you.
>>>>
>>>>>
>>>>>>
>>>>>> Thank you.
>>>>>>
>>>>>
>>>>>
>>>>> --
>>>>> Adrian Klaver
>>>>> adrian.kla...@aklaver.com
>>>
>>>
>>>
>>>
>>> --
>>> Adrian Klaver
>>> adrian.kla...@aklaver.com
>
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com

Re: ODBC - Getting CONN ERROR: errmsg='The buffer was too small for the InfoValue'

[Igor Korot]

,Hi,

On Wed, Jul 11, 2018 at 10:33 PM, Edgard Battisti Guimarães
 wrote:
> I've ported a powerbuilder application  to postgresql. Tested ok on windows
> 10 64, was installed on two win10-64 other computers, all connecting the
> postgresql database in the localhost with access via odbc. The third of them
> presented the error detailed below in the logs (mylog and psqlodbc)
> integrally copy and paste.

Which database the application connecting to initially?
Can you show the PB script that connects to the DB?
Can you show the credentials you are trying to connect with?
Can you turn on ODBC logging and send the log with the failure?

Thank you.

>
> --
> Edgard Battisti Guimarães
>

Re: How to watch for schema changes

[Igor Korot]

Hi, Adrian,

On Wed, Jul 11, 2018 at 11:12 PM, Adrian Klaver
 wrote:
> On 07/11/2018 08:46 PM, Igor Korot wrote:
>>
>> Hi, guys,
>>
>>
>> On Mon, Jul 9, 2018 at 5:38 PM, Adrian Klaver 
>> wrote:
>>>
>>> On 07/09/2018 01:49 PM, Igor Korot wrote:
>>>>
>>>>
>>>> Hi, Adrian
>>>>
>>>> On Tue, Jul 3, 2018 at 1:24 PM, Adrian Klaver
>>>> 
>>>> wrote:
>>>>>
>>>>>
>>>>> On 07/03/2018 11:15 AM, Igor Korot wrote:
>>>>>>
>>>>>>
>>>>>>
>>>>>> Adrian,
>>>>>>
>>>>>> On Tue, Jul 3, 2018 at 12:32 PM, Adrian Klaver
>>>>>>  wrote:
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>> On 07/03/2018 10:21 AM, Igor Korot wrote:
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>> Hi, ALL,
>>>>>>>> Is there any trigger or some other means I can do on the server
>>>>>>>> which will watch for CREATE/ALTER/DROP TABLE command and after
>>>>>>>> successful
>>>>>>>> execution of those will issue a NOTIFY statement?
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>> https://www.postgresql.org/docs/10/static/event-triggers.html
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>> According to the documentation the lowest version it supports is 9.3.
>>>>>> Anything prior to that?
>>>>>>
>>>>>> I'm working with OX 10.8 and it has 9.1 installed.
>>>>>
>>>>>
>>>>>
>>>>> 9.1 went EOL almost two years ago. The oldest supported version is 9.3,
>>>>> though it will go EOL this September:
>>>>>
>>>>> https://www.postgresql.org/support/versioning/
>>>>>
>>>>> Are you forced to work with 9.1 or can you use something from here:
>>>>>
>>>>> https://www.postgresql.org/download/macosx/
>>>>>
>>>>> to get a newer version? FYI that will be a major upgrade so will
>>>>> require
>>>>> a
>>>>> dump/restore or use of pg_upgrade.
>>>>
>>>>
>>>>
>>>> Just a thought...
>>>> Is it possible to create a trigger for a system table? Or this
>>>> operation is restricted
>>>
>>>
>>>
>>> Easy enough to test. As postgres super user:
>>>
>>> test_(postgres)# create trigger info_test before insert on pg_class
>>> execute
>>> procedure ts_update();
>>>
>>> ERROR:  permission denied: "pg_class" is a system catalog
>>
>>
>> But
>>
>> draft=# CREATE TRIGGER info_test AFTER INSERT ON
>> information_schema.tables EXECUTE PROCEDURE test();
>> ERROR:  function test() does not exist
>>
>> So it looks like this should be possible?
>
>
> No, see:
>
> https://www.postgresql.org/docs/10/static/sql-createtrigger.html
>
> AFTER trigger on views are STATEMENT level only.

But I do have access to the STATEMENT right?

Thank you.

>
> https://www.postgresql.org/docs/10/static/plpgsql-trigger.html
>
> "NEW
>
> Data type RECORD; variable holding the new database row for
> INSERT/UPDATE operations in row-level triggers. This variable is unassigned
> in statement-level triggers and for DELETE operations.
> OLD
>
> Data type RECORD; variable holding the old database row for
> UPDATE/DELETE operations in row-level triggers. This variable is unassigned
> in statement-level triggers and for INSERT operations.
> "
>
> So you won't know what was INSERTed in row.
>
>
>>
>> Thank you.
>>
>>>
>>>
>>>> for when the server is actually being set-up?
>>>>
>>>> Successful "CREATE TABLE..." statement creates a row inside the
>>>> information_schema.tables
>>>> so if I can create a trigger after this record is inserted or deleted
>>>> that should be fine.
>>>>
>>>> Thank you.
>>>>
>>>>>
>>>>>>
>>>>>> And a second question - how do I work with it?
>>>>>> I presume that function will have to be compiled in its own module
>>>>>> (either dll, so or dylib).
>>>>>> But then from the libpq interface how do I call it?
>>>>>
>>>>>
>>>>>
>>>>>
>>>>> It can use functions written in PL languages. See below:
>>>>>
>>>>> https://www.postgresql.org/docs/10/static/sql-createeventtrigger.html
>>>>>
>>>>> for an example written in plpgsql.
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>>
>>>>>> Thank you.
>>>>>>
>>>>>>>
>>>>>>>>
>>>>>>>> Thank you.
>>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>> --
>>>>>>> Adrian Klaver
>>>>>>> adrian.kla...@aklaver.com
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>> --
>>>>> Adrian Klaver
>>>>> adrian.kla...@aklaver.com
>>>
>>>
>>>
>>>
>>> --
>>> Adrian Klaver
>>> adrian.kla...@aklaver.com
>>
>>
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com

Re: How to watch for schema changes

[Igor Korot]

Hi,


On Thu, Jul 12, 2018 at 12:16 PM, David G. Johnston
 wrote:
> On Thu, Jul 12, 2018 at 8:50 AM, Igor Korot  wrote:
>>
>> > No, see:
>> >
>> > https://www.postgresql.org/docs/10/static/sql-createtrigger.html
>> >
>> > AFTER trigger on views are STATEMENT level only.
>>
>> But I do have access to the STATEMENT right?
>
>
> Yes, except nothing in the system actually attempts to directly target
> information_schema views with updates so there will never be a triggering
> event.
>
> A normal trigger will not work - which is a large reason why event triggers
> were implemented.
>
> All of the alternative ideas (which I think was just log file parsing)
> you've decided are not viable for your need.  Thus you've seemingly
> eliminated all viable options and you now need to make a business decision.

[code]
MyMac:/ igorkorot$ find . -name postgresql.conf
find: ./.DocumentRevisions-V100: Permission denied
find: ./.fseventsd: Permission denied
find: ./.Spotlight-V100: Permission denied
find: ./.Trashes: Permission denied
find: ./dev/fd/3: Not a directory
find: ./dev/fd/4: Not a directory
find: ./Library/Application Support/Apple/ParentalControls/Users:
Permission denied
find: ./Library/Caches/com.apple.Spotlight/schema.501.plist: Permission denied
find: ./Library/Caches/com.apple.Spotlight/schema.502.plist: Permission denied
find: ./Library/Caches/com.apple.Spotlight/schema.89.plist: Permission denied
find: ./Library/Caches/com.apple.Spotlight: Permission denied
find: ./Library/PostgreSQL/9.1/data: Permission denied
find: ./private/etc/cups/certs: Permission denied
find: ./private/etc/raddb/certs: Permission denied
find: ./private/etc/raddb/modules: Permission denied
find: ./private/etc/raddb/sites-available: Permission denied
find: ./private/etc/raddb/sites-enabled: Permission denied
find: ./private/etc/raddb/sql: Permission denied
find: ./private/tmp/launchd-158.ac7XMn: Permission denied
find: ./private/tmp/launchd-47725.RroMYY: Permission denied
find: ./private/tmp/launchd-49727.qQpnIz: Permission denied
find: ./private/var/agentx: Permission denied
find: ./private/var/at/tabs: Permission denied
find: ./private/var/at/tmp: Permission denied
find: ./private/var/audit: Permission denied
find: ./private/var/backups: Permission denied
find: ./private/var/db/ConfigurationProfiles/Setup: Permission denied
find: ./private/var/db/dhcpclient: Permission denied
find: ./private/var/db/dslocal/nodes/Default: Permission denied
find: ./private/var/db/geod: Permission denied
find: ./private/var/db/krb5kdc: Permission denied
find: ./private/var/db/launchd.db/com.apple.launchd.peruser.0: Permission denied
find: ./private/var/db/launchd.db/com.apple.launchd.peruser.200:
Permission denied
find: ./private/var/db/launchd.db/com.apple.launchd.peruser.202:
Permission denied
find: ./private/var/db/launchd.db/com.apple.launchd.peruser.212:
Permission denied
find: ./private/var/db/launchd.db/com.apple.launchd.peruser.502:
Permission denied
find: ./private/var/db/launchd.db/com.apple.launchd.peruser.88:
Permission denied
find: ./private/var/db/launchd.db/com.apple.launchd.peruser.89:
Permission denied
find: ./private/var/db/launchd.db/com.apple.launchd.peruser.92:
Permission denied
find: ./private/var/db/launchd.db/com.apple.launchd.peruser.97:
Permission denied
find: ./private/var/db/locationd: Permission denied
find: ./private/var/db/Spotlight: Permission denied
find: ./private/var/db/sudo: Permission denied
find: ./private/var/folders/r9/_xhmtynj7xj22mwt_dcfbxqrgp/C:
Permission denied
find: ./private/var/folders/r9/_xhmtynj7xj22mwt_dcfbxqrgp/T:
Permission denied
find: 
./private/var/folders/zz/zyxvpxvq6csfxvn_n0/0/com.apple.revisiond.temp:
Permission denied
find: ./private/var/folders/zz/zyxvpxvq6csfxvn_n0/C:
Permission denied
find: ./private/var/folders/zz/zyxvpxvq6csfxvn_n0/Cleanup
At Startup: Permission denied
find: ./private/var/folders/zz/zyxvpxvq6csfxvn_n0/T:
Permission denied
find: ./private/var/folders/zz/zyxvpxvq6csfxvn_n0300r/T:
Permission denied
find: ./private/var/folders/zz/zyxvpxvq6csfxvn_n08421/C:
Permission denied
find: ./private/var/folders/zz/zyxvpxvq6csfxvn_n08421/T:
Permission denied
find: ./private/var/folders/zz/zyxvpxvq6csfxvn_n08w27/C:
Permission denied
find: ./private/var/folders/zz/zyxvpxvq6csfxvn_n08w27/T:
Permission denied
find: ./private/var/folders/zz/zyxvpxvq6csfxvn_n0b02r/T:
Permission denied
find: ./private/var/folders/zz/zyxvpxvq6csfxvn_n0b42s/C:
Permission denied
find: ./private/var/folders/zz/zyxvpxvq6csfxvn_n0b42s/T:
Permission denied
find: ./private/var/folders/zz/zyxvpxvq6csfxvn_n0bh2w/C:
Permission denied
find: ./private/var/folders/zz/zyxvpxvq6csfxvn_n0bh2w/T:
Permission denied
find: ./private/var/folders/zz/zyxvpxvq6csfxvn_n0c431/C:
Perm

Re: How to watch for schema changes

[Igor Korot]

Hi,


On Thu, Jul 12, 2018 at 7:45 PM, Rob Sargent  wrote:
>
>> [code]
>> MyMac:/ igorkorot$ find . -name postgresql.conf
>> find: ./.DocumentRevisions-V100: Permission denied
>> find: ./.fseventsd: Permission denied
>> find: ./.Spotlight-V100: Permission denied
>> find: ./.Trashes: Permission denied
>> find: ./dev/fd/3: Not a directory
>> find: ./dev/fd/4: Not a directory
>> find: ./Library/Application Support/Apple/ParentalControls/Users:
>> Permission denied
>> find: ./Library/Caches/com.apple.Spotlight/schema.501.plist: Permission
>> denied
>> find: ./Library/Caches/com.apple.Spotlight/schema.502.plist: Permission
>> denied
>> find: ./Library/Caches/com.apple.Spotlight/schema.89.plist: Permission
>> denied
>> find: ./Library/Caches/com.apple.Spotlight: Permission denied
>> find: ./Library/PostgreSQL/9.1/data: Permission denied
>> find: ./private/etc/cups/certs: Permission denied
>> find: ./private/etc/raddb/certs: Permission denied
>> find: ./private/etc/raddb/modules: Permission denied
>> find: ./private/etc/raddb/sites-available: Permission denied
>> find: ./private/etc/raddb/sites-enabled: Permission denied
>> find: ./private/etc/raddb/sql: Permission denied
>> find: ./private/tmp/launchd-158.ac7XMn: Permission denied
>> find: ./private/tmp/launchd-47725.RroMYY: Permission denied
>> find: ./private/tmp/launchd-49727.qQpnIz: Permission denied
>> find: ./private/var/agentx: Permission denied
>> find: ./private/var/at/tabs: Permission denied
>> find: ./private/var/at/tmp: Permission denied
>> find: ./private/var/audit: Permission denied
>> find: ./private/var/backups: Permission denied
>> find: ./private/var/db/ConfigurationProfiles/Setup: Permission denied
>> find: ./private/var/db/dhcpclient: Permission denied
>> find: ./private/var/db/dslocal/nodes/Default: Permission denied
>> find: ./private/var/db/geod: Permission denied
>> find: ./private/var/db/krb5kdc: Permission denied
>> find: ./private/var/db/launchd.db/com.apple.launchd.peruser.0: Permission
>> denied
>> find: ./private/var/db/launchd.db/com.apple.launchd.peruser.200:
>> Permission denied
>> find: ./private/var/db/launchd.db/com.apple.launchd.peruser.202:
>> Permission denied
>> find: ./private/var/db/launchd.db/com.apple.launchd.peruser.212:
>> Permission denied
>> find: ./private/var/db/launchd.db/com.apple.launchd.peruser.502:
>> Permission denied
>> find: ./private/var/db/launchd.db/com.apple.launchd.peruser.88:
>> Permission denied
>> find: ./private/var/db/launchd.db/com.apple.launchd.peruser.89:
>> Permission denied
>> find: ./private/var/db/launchd.db/com.apple.launchd.peruser.92:
>> Permission denied
>> find: ./private/var/db/launchd.db/com.apple.launchd.peruser.97:
>> Permission denied
>> find: ./private/var/db/locationd: Permission denied
>> find: ./private/var/db/Spotlight: Permission denied
>> find: ./private/var/db/sudo: Permission denied
>> find: ./private/var/folders/r9/_xhmtynj7xj22mwt_dcfbxqrgp/C:
>> Permission denied
>> find: ./private/var/folders/r9/_xhmtynj7xj22mwt_dcfbxqrgp/T:
>> Permission denied
>> find:
>> ./private/var/folders/zz/zyxvpxvq6csfxvn_n0/0/com.apple.revisiond.temp:
>> Permission denied
>> find: ./private/var/folders/zz/zyxvpxvq6csfxvn_n0/C:
>> Permission denied
>> find: ./private/var/folders/zz/zyxvpxvq6csfxvn_n0/Cleanup
>> At Startup: Permission denied
>> find: ./private/var/folders/zz/zyxvpxvq6csfxvn_n0/T:
>> Permission denied
>> find: ./private/var/folders/zz/zyxvpxvq6csfxvn_n0300r/T:
>> Permission denied
>> find: ./private/var/folders/zz/zyxvpxvq6csfxvn_n08421/C:
>> Permission denied
>> find: ./private/var/folders/zz/zyxvpxvq6csfxvn_n08421/T:
>> Permission denied
>> find: ./private/var/folders/zz/zyxvpxvq6csfxvn_n08w27/C:
>> Permission denied
>> find: ./private/var/folders/zz/zyxvpxvq6csfxvn_n08w27/T:
>> Permission denied
>> find: ./private/var/folders/zz/zyxvpxvq6csfxvn_n0b02r/T:
>> Permission denied
>> find: ./private/var/folders/zz/zyxvpxvq6csfxvn_n0b42s/C:
>> Permission denied
>> find: ./private/var/folders/zz/zyxvpxvq6csfxvn_n0b42s/T:
>> Permission denied
>> find: ./private/var/folders/zz/zyxvpxvq6csfxvn_n0bh2w/C:
>> Permission denied
>> find: ./private/var/folders/zz/zyxvpxvq6csfxvn_n0bh2w/T:
>> Permission denied
>> find: ./private/var/folders/zz/zyxvpxvq6csfxvn_n0c431/C:
>> Permission denied
>> find: ./private/var/folders/zz/zyxvpxvq6csfxvn_n0c431/T:
>> Permission denied
>> find: ./private/var/folders/zz/zyxvpxvq6csfxvn_n0s068/C:
>> Permission denied
>> find: ./private/var/folders/zz/zyxvpxvq6csfxvn_n0s068/T:
>> Permission denied
>> find: ./private/var/folders/zz/zyxvpxvq6csfxvn_n0s86_/C:
>> Permission denied
>> find: ./private/var/folders/zz/zyxvpxvq6csfxvn_n0s86_/T:
>> Permission denied
>> find: ./private/var/folders/zz/zyxvpxvq6csfxvn_n0sm6d/C:
>> Permission denied
>> find: ./private/var/folders/zz/zy

Problem building libpq

[Igor Korot]

Hi,
I recently updated my compiler going from MSVC 2010 to MSVC 2017.
Now I'm trying to re-build the libpq with it.

[code]
NMAKE : fatal error U1064: MAKEFILE not found and no target specified
Stop.

C:\Program Files (x86)\Microsoft Visual Studio\2017\Community>cd c:\Users\Igor

c:\Users\Igor>nmake

Microsoft (R) Program Maintenance Utility Version 14.14.26433.0
Copyright (C) Microsoft Corporation.  All rights reserved.

NMAKE : fatal error U1064: MAKEFILE not found and no target specified
Stop.

c:\Users\Igor>cd OneDrive\Documents\dbhandler_app\libpg

c:\Users\Igor\OneDrive\Documents\dbhandler_app\libpg>nmake /f win32.mak DEBUG=1

Microsoft (R) Program Maintenance Utility Version 14.14.26433.0
Copyright (C) Microsoft Corporation.  All rights reserved.

cd include
NMAKE : fatal error U1077: 'cd' : return code '0x1'
Stop.
[/code]

What am I missing?

Thank you.

Re: PostgreSQL C Language Extension with C++ Code

[Igor Korot]

Hi,

On Sun, Aug 12, 2018 at 12:05 PM, TalGloz  wrote:
> Hi,
>
> I've searched information about my problem in the archives and on the
> internet, but it didn't help. I have this small myfunc.cpp
>
> 1:   #include 
> 2:   #include 
> 3:   #include 
> 4:   #include 
> 5:   #include 
> 6:   #include 
> 7:   #include 
> 8:   #include 
> 9:   #include 
> 10: #include 
> 11: #include 
> 12: #include 
> 13: #include 
> 14: #include  // external compiled c++ library linked on
> running 'make'
> 15:
> 16: #ifdef PG_MODULE_MAGIC

I think you're missing  "n" here.
Should be:

#ifndef PG_MODULE_MAGIC

.

Thank you.

> 17: PG_MODULE_MAGIC;
> 18: #endif
> 19:
> 20: Datum sum_of_numbers(PG_FUNCTION_ARGS);
> 21: PG_FUNCTION_INFO_V1(sum_of_numbers);
> 22:
> 23: extern "C" {
> 24: int64_t sum_of_numbers(){
> 25: std::vector numbers {23, 445, 64};
> 26: auto sum = 0;
> 27: for (auto &item : numbers){
> 28: sum += item;
> 29: }
> 30: return sum;
> 31: }
> 32: }
>
> And I compile without any problem suing this Makefiles:
>
> 1:   MODULES = myfunc
> 2:
> 3:   PG_CONFIG = /usr/pgsql-10/bin/pg_config
> 4:   PGXS = $(shell $(PG_CONFIG) --pgxs)
> 5:   INCLUDEDIR = $(shell $(PG_CONFIG) --includedir-server)
> 6:   INCLUDE_SEAL = /usr/local/include/seal
> 7:   INCLUDE_SEAL_LIB = /usr/local/lib/libseal.a
> 8:   INCLUDE_CPPCODEC = /usr/local/include/cppcodec
> 9:   #CFLAGS = -std=c11
> 10: #CFLAGS = --std=c++14 -fPIC -Wall -Werror -g3 -O0
> 11: include $(PGXS)
> 12: myfunc.so: myfunc.o
> 13: g++ -shared -o myfunc.so myfunc.o
> 14:
> 16: myfunc.o: myfunc.cpp
> 17:g++ --std=c++17 -fPIC -Wall -Werror -g3 -O0 -o myfunc.o -c
> myfunc.cpp -I$(INCLUDEDIR) -L$(INCLUDE_SEAL_LIB) -I$(INCLUDE_SEAL)
> -I$(INCLUDE_CPPCODEC)
> 18:
>
> After copying the myfunc.so file to the PostgreSQL lib folder and executing:
>
> 1: CREATE OR REPLACE FUNCTION
> 2:sum_of_numbers() RETURNS integer AS 'myfunc'
> 3: LANGUAGE C STRICT;
>
> I get this error:
> *ERROR:  incompatible library "/usr/pgsql-10/lib/myfunc.so": missing magic
> block
> HINT:  Extension libraries are required to use the PG_MODULE_MAGIC macro.
> SQL state: XX000*
>
> I've added the files and line numbers for an easier discussion.
>
> Thanks a lot,
> Tal
>
> myfunc.cpp 
> Makefiles.Makefiles
> 
>
>
>
> --
> Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html
>

Re: How to watch for schema changes

[Igor Korot]

Hi, Melvin,

On Tue, Jul 3, 2018 at 2:00 PM Melvin Davidson  wrote:
>
>
>
> >Unfortunately I'm stuck with 9.1.
>
> Have you thought about just setting   log_statement = 'ddl' in postgresql.conf
> and just greping the log for CREATE and ALTER?

Is there a way to query a server for a place where the log file is?
Or I will have to hard-code it?

Thank you.

>

Re: How to watch for schema changes

[Igor Korot]

Hi,

On Mon, Sep 17, 2018 at 9:19 PM Christophe Pettus  wrote:
>
>
> > On Sep 17, 2018, at 07:09, Igor Korot  wrote:
> >
> > Is there a way to query a server for a place where the log file is?
>
> SHOW log_directory;
>
> It's either relative to the PGDATA directory, or an absolute path.

And I presume it depends on the string I put in the log file?

Thank you.

>
> --
> -- Christophe Pettus
>x...@thebuild.com
>

Fwd: What is the problem with this code?

[Igor Korot]

Does anybody have an idea why the code below fails?

Thank you.


-- Forwarded message -
From: Igor Korot 
Date: Thu, Oct 18, 2018 at 11:49 PM
Subject: What is the problem with this code?
To: PostgreSQL ODBC list 


Hi, ALL,

[code]
std::wstring query1 = L"SHOW log_directory";
std::wstring query2 = L"SHOW log_filename";
SQLWCHAR *qry1 = new SQLWCHAR[query1.length() + 2];
SQLWCHAR *qry2 = new SQLWCHAR[query2.length() + 2];
memset( qry1, '\0', query1.length() + 2 );
memset( qry2, '\0', query2.length() + 2 );
uc_to_str_cpy( qry1, query1 );
uc_to_str_cpy( qry2, query2 );
RETCODE ret = SQLAllocHandle( SQL_HANDLE_STMT, m_hdbc, &m_hstmt );
if( ret == SQL_SUCCESS || ret == SQL_SUCCESS_WITH_INFO )
{
ret = SQLPrepare( m_hstmt, qry1, SQL_NTS );
if( ret != SQL_SUCCESS && ret != SQL_SUCCESS_WITH_INFO )
{
GetErrorMessage( errorMsg, 1 );
result = 1;
}
else
{
ret = SQLDescribeCol( m_hstmt, 1, columnName, 256,
&columnNameLen, &columnDataType, &columnDataSize, &columnDataDigits,
&columnDataNullable );
if( ret != SQL_SUCCESS && ret != SQL_SUCCESS_WITH_INFO )
{
GetErrorMessage( errorMsg, 1 );
result = 1;
}
[/code]

The SQLDescribeCol() call fails with the error invalid column number".

Does anybody have any idea?

Thank you.

Re: Query plan: SELECT vs INSERT from same select

[Igor Korot]

Hi,

On Tue, Jul 23, 2019 at 3:29 PM Alexander Voytsekhovskyy
 wrote:
>
> I have quite complicated query:
>
> SELECT axis_x1, axis_y1, SUM(delivery_price)  as v_1 FROM (
> SELECT to_char(delivery_data.delivery_date, '-MM') as axis_x1, 
> clients.id_client as axis_y1, delivery_data.amount * production_price.price * 
> groups.discount as delivery_price
>
> FROM delivery_data
> JOIN client_tt ON (client_tt.id_client_tt = delivery_data.id_client_tt)
> JOIN clients ON (client_tt.id_client = clients.id_client)
> JOIN production ON (production.id = delivery_data.id_product)
> JOIN groups ON (groups.id = delivery_data.delivery_group_id AND 
> client_tt.id_group = groups.id AND groups.id = clients.id_group)
> LEFT JOIN production_price on (delivery_data.id_product = 
> production_price.id_production AND groups.price_list_id = 
> production_price.price_list_id AND delivery_data.delivery_date BETWEEN 
> production_price.date_from AND production_price.date_to)
>
> WHERE delivery_data.delivery_date between '2019-03-01' AND '2019-06-30'
> AND delivery_data.delivery_group_id IN (...short list of values...)
> AND delivery_data.id_product IN ()) AS tmpsource
>
> WHERE TRUE
> GROUP BY GROUPING SETS ((axis_x1, axis_y1), (axis_x1), (axis_y1), ())
>
> It runs well, took 1s and returns 4000 rows.
>
> You can see explain analyze verbose here:
> https://explain.depesz.com/s/AEWj
>
> The problem is, when i wrap it to
>
> A)
> INSERT INTO norepl_1542_result (axis_x1, axis_y1, v_1)
> SELECT  SAME QUERY
>
> OR even
>
> B)
> WITH rows AS (
> ... SAME SELECT QUERY ...
> )
> INSERT INTO norepl_1542_result (axis_x1, axis_y1, v_1)
> SELECT * FROM rows
>
> The query time dramatically drops to 500+ seconds.
>
> You can see explain analyze verbose here
> https://explain.depesz.com/s/AEWj
>
> As you can see, 100% of time goes to same SELECT query, there is no issues 
> with INSERT-part
>
> I have played a lot and it's reproducing all time.
>
> So my question is, why wrapping SELECT query with INSERT FROM SELECT 
> dramatically change query plan and make it 500x slower?

Which version of PostgreSQL do you have?
Which OS does it running on?

Thank you.

Re: Recomended front ends?

[Igor Korot]

Hi,

On Wed, Aug 7, 2019 at 1:57 PM stan  wrote:
>
> I am in the process of defining an application for a very small company
> that uses Postgresql for the backend DB. This DB will eventually run on a
> hosted machine. As you imagine all of the employees have Windows machines
> for their normal work asks. Frankly I am not very strong on Windows. so I
> am wondering what the consensus is for creating forms and reports?
>
> My first though is Libre Office as that is cross platform, and i can test
> on my development Linux machine. However, i am getting a bit of push-back
> from the user as he is having issues with installing Libre Office on his
> computer. he says it does not play well with MS Office. Also we seem to be
> having some bugs with Libre Office Base in early development.
>
> What is the community wisdom here?

What language/tools you are most comfortable with?

Thank you.

>
> --
> "They that would give up essential liberty for temporary safety deserve
> neither liberty nor safety."
> -- Benjamin Franklin
>
>

Re: Recomended front ends?

[Igor Korot]

Hi,

On Wed, Aug 7, 2019 at 1:57 PM stan  wrote:
>
> I am in the process of defining an application for a very small company
> that uses Postgresql for the backend DB. This DB will eventually run on a
> hosted machine. As you imagine all of the employees have Windows machines
> for their normal work asks. Frankly I am not very strong on Windows. so I
> am wondering what the consensus is for creating forms and reports?
>
> My first though is Libre Office as that is cross platform, and i can test
> on my development Linux machine. However, i am getting a bit of push-back
> from the user as he is having issues with installing Libre Office on his
> computer. he says it does not play well with MS Office. Also we seem to be
> having some bugs with Libre Office Base in early development.
>
> What is the community wisdom here?

On top of what already been said - make sure that the product you are
about to start
working on will have its requirements clear and concise.

What is expected from the software?
Does it needs to go out and access the web?
Is the company split between different areas of the country/state?
Does it needs to support Windows only?
Will there be a need to a handheld device or bar code scanner?
Will printing be involved?

List is preliminary and can go on and on. Its just first that comes to mind.

Get the requirements from the company management, make sure you understand them
check you knowledge of different tools available and their support of
the feature requested
and start working.

Good luck!!

Thank you.

>
> --
> "They that would give up essential liberty for temporary safety deserve
> neither liberty nor safety."
> -- Benjamin Franklin
>
>

Re: Problems modifyiong view

[Igor Korot]

Hi,

On Thu, Nov 14, 2019 at 7:54 AM stan  wrote:
>
> I am trying to add columns to a view using CREATE OR REPLACE VIEW, and I am
> getting the following error:
>
> ERROR:  cannot change name of view column "descrip" to "contact_person_1"
>
> I suppose  I can drop the view, and recreate it, but that seems to indicate
> that the create or replace functionality is not functioning the way I would
> expect.
>
> Am I missing something here?

What version?
What OS server is running on?
What client are you running?

Thank you.

>
> --
> "They that would give up essential liberty for temporary safety deserve
> neither liberty nor safety."
> -- Benjamin Franklin
>
>

Re: Connecting Powerbuilder / EAserver to PostgreSQL server 11 via ODBC or JDBC

[Igor Korot]

Hi,



On Fri, Jun 26, 2020, 8:31 AM Matthias Apitz  wrote:

>
> Hello,
>
> After the very successful migration of our Library Management System
> (which uses ESQL/C, DBI, JDBC) together with PostgreSQL 11.4, we want to
> do the
> same with another LMS written in Powerbuild, running in an EAServer and
> currently using Sybase ASE as DBS.
>
> There is an error situation already on the connection phase, the ODBC
> debug logs show that the connection establishment and authentication to
> the PostgreSQL server is fine (also the disconnect), but the EAServer
> makes an error out of this and returns to the Powerbuilder software that
> the connection is invalid,
> raising some error 999 without saying much in the log files what this
> could mean or is caused from.
>

So what operation is eroding out?
Can you try to isolate it?

Thank you.


> I know this is difficult to analyze with all this proprietary software
> stack, but my first question here is: anybody out here who could manage
> such an architecture successful working?
>
> And please do not send hints of the type, rewrite everything in Java or
> Visual Basic, as this is not an option :-)
>
> Thanks
>
> matthias
> --
> Matthias Apitz, ✉ g...@unixarea.de, http://www.unixarea.de/
> +49-176-38902045
> Public GnuPG key: http://www.unixarea.de/key.pub
>
>
>

Re: Need free PG odbc driver for Windows 10

[Igor Korot]

Hi,

On Tue, Jul 28, 2020 at 11:40 AM David Gauthier
 wrote:
>
> Hi:
>
> I need a free odbc driver for PG to be installed on Windows 10 that my user 
> community can easily install.  By "easily install" I mean no binaries, no 
> zipped file, etc... just point-click-install (with the usual confirmations 
> and accepting default destinations for the code and such).
>
> Devart used to give this away and I have a copy on my laptop.  But apparently 
> it's not free anymore.
>
> If it matters, the PG DB they will be accessing is on linux, psql (9.3.2, 
> server 11.3)

If I understand correctly, there is a free ODBC driver on the
PostgreSQL download page.

Thank you.

>
> Thanks !

How to write such a query

[Igor Korot]

Hi,
Consider following

[code]
CREATE TABLE X(id INTEGER PRIMARY KEY, field1 char(50), field2 int);
CREATE TABLE Y(id INTEGER PRIMARY KEY, field1 char, field2 double(10, 2));
SELECT X.field1, Y.field2 from X, Y WHERE X.id = Y.id;
[/code]

Assuming that the SELECT return 10 rows, I want to update X.field1
in row 5.

How do I write a WHERE clause in the

[code]
UPDATE X.field1 SET X.field1 = '' WHERE
[/code]

Thank you.

Re: How to write such a query

[Igor Korot]

Hi, Paul

On Fri, Sep 18, 2020 at 12:34 PM Paul Förster  wrote:
>
> Hi Igor,
>
> > On 18. Sep, 2020, at 19:29, Igor Korot  wrote:
> >
> > Hi,
> > Consider following
> >
> > [code]
> > CREATE TABLE X(id INTEGER PRIMARY KEY, field1 char(50), field2 int);
> > CREATE TABLE Y(id INTEGER PRIMARY KEY, field1 char, field2 double(10, 2));
> > SELECT X.field1, Y.field2 from X, Y WHERE X.id = Y.id;
> > [/code]
> >
> > Assuming that the SELECT return 10 rows, I want to update X.field1
> > in row 5.
> >
> > How do I write a WHERE clause in the
> >
> > [code]
> > UPDATE X.field1 SET X.field1 = '' WHERE
> > [/code]
> >
> > Thank you.
>
> update x set field1='' where id=5;

How do you know that the row #5 will have an X.id field 5?

Thank you.

>
> Cheers,
> Paul

Re: How to write such a query

[Igor Korot]

Hi, Johnathan,

On Fri, Sep 18, 2020 at 12:34 PM Jonathan Strong 
wrote:

> Are you looking to arbitrarily update the field in the fifth row, or can
> the row that needs to be updated be isolated by some add'l attribute?
> What's the use case?
>

What do you mean?
I don't have any other attributes.

I want to understand how to emulate MS Access behavior, where you have a
form
with the arbitrary query, then you can go to any record in that form and
update any field.

Is it even possible from the "pure SQL" POV? Or Access is doing some
VBA/DB/4GL magic?

Thank you.


> - Jon
>
> <https://www.linkedin.com/in/jonstrong/>
> <https://www.jonathanrstrong.com>
>
> *Jonathan Strong*
>
> CIO / CTO / Consultant
>
> *P:* 609-532-1715 *E:* jonathanrstr...@gmail.com
>
> *Quora Top Writer <https://www.quora.com/profile/Jonathan-R-Strong>*
>
>
> On Fri, Sep 18, 2020 at 1:27 PM Igor Korot  wrote:
>
>> Hi,
>> Consider following
>>
>> [code]
>> CREATE TABLE X(id INTEGER PRIMARY KEY, field1 char(50), field2 int);
>> CREATE TABLE Y(id INTEGER PRIMARY KEY, field1 char, field2 double(10, 2));
>> SELECT X.field1, Y.field2 from X, Y WHERE X.id = Y.id;
>> [/code]
>>
>> Assuming that the SELECT return 10 rows, I want to update X.field1
>> in row 5.
>>
>> How do I write a WHERE clause in the
>>
>> [code]
>> UPDATE X.field1 SET X.field1 = '' WHERE
>> [/code]
>>
>> Thank you.
>>
>>
>>

Re: How to write such a query

[Igor Korot]

Hi, Adrian,

On Fri, Sep 18, 2020 at 12:58 PM Adrian Klaver
 wrote:
>
> On 9/18/20 10:46 AM, Igor Korot wrote:
> > Hi, Johnathan,
> >
> > On Fri, Sep 18, 2020 at 12:34 PM Jonathan Strong
> > mailto:jonathanrstr...@gmail.com>> wrote:
> >
> > Are you looking to arbitrarily update the field in the fifth row, or
> > can the row that needs to be updated be isolated by some add'l
> > attribute? What's the use case?
> >
> >
> > What do you mean?
> > I don't have any other attributes.
> >
> > I want to understand how to emulate MS Access behavior, where you have a
> > form
> > with the arbitrary query, then you can go to any record in that form and
> > update any field.
> >
> > Is it even possible from the "pure SQL" POV? Or Access is doing some
> > VBA/DB/4GL magic?
> >
>
> When you are updating a record in a form the framework(Access in your
> case) is using some identifier from that record to UPDATE that
> particular record in the database. From when I used Access, I seem to
> remember it would not give you INSERT/UPDATE capability on a form unless
> you had specified some unique key for the records. So you need to find
> what the key(generally a PRIMARY KEY) is and use that to do the UPDATE.

But now the question becomes

How to find what the primary key (or UNIQUE identifier) value is
for row 5 in the recordset?

Thank you.

>
> > Thank you.
> >
>
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com

Re: How to write such a query

[Igor Korot]

Hi, Ken,

On Fri, Sep 18, 2020 at 2:46 PM Ken Tanzer  wrote:

> > How to find what the primary key (or UNIQUE identifier) value is
>> > for row 5 in the recordset?
>>
>> You're missing the point: as mentioned before, there is no "row 5". To
>> update the 5th record that you've fetched, you increment a counter each
>> time
>> you fetch a row, and when you read #5, do an UPDATE X SET field1 =
>> 'blarg'
>> WHERE id = ;
>>
>>
> It seems worth mentioning for benefit of the OPs question that there _is_
> a way to get a row number within a result set.  Understanding and making
> good use of that is an additional matter.
>
> SELECT X.field1, Y.field2*,row_number() OVER ()*  from X, Y WHERE X.id =
> Y.id -- ORDER BY ?
>
> That row number is going to depend on the order of the query, so it might
> or might not have any meaning.  But if you queried with a primary key and a
> row number, you could then tie the two together and make an update based on
> that.
>

Thank you for the info.
My problem is that I want to emulate Access behavior.

As I said - Access does it without changing the query internally (I
presume).

I want to do the same with PostgreSQL.

I'm just trying to understand how to make it work for any query

I can have 3,4,5 tables, query them and then update the Nth record in the
resulting recordset.

Access does it, PowerBuilder does it.

I just want to understand how.

Thank you.


> Cheers,
> Ken
> --
> AGENCY Software
> A Free Software data system
> By and for non-profits
> *http://agency-software.org/ *
> *https://demo.agency-software.org/client
> *
> ken.tan...@agency-software.org
> (253) 245-3801
>
> Subscribe to the mailing list
>  to
> learn more about AGENCY or
> follow the discussion.
>

Re: How to write such a query

[Igor Korot]

Ken,

On Fri, Sep 18, 2020 at 3:35 PM Ken Tanzer  wrote:

> On Fri, Sep 18, 2020 at 1:26 PM Ron  wrote:
>
>> On 9/18/20 3:18 PM, Igor Korot wrote:
>>
> Thank you for the info.
>> My problem is that I want to emulate Access behavior.
>>
>> As I said - Access does it without changing the query internally (I
>> presume).
>>
>> I want to do the same with PostgreSQL.
>>
>> I'm just trying to understand how to make it work for any query
>>
>> I can have 3,4,5 tables, query them and then update the Nth record in the
>> resulting recordset.
>>
>> Access does it, PowerBuilder does it.
>>
>> I just want to understand how.
>>
>>
>> They do it by hiding the details from you.
>>
>>
> That's true.  And Igor--people are asking you some good questions about
> why and design and such that you'd probably be well-advised to think about
> and respond to.
>
> So I'm not saying you should do this, but responding to your question
> specifically, and what the "details" are that Ron alludes to, one way to
> get the result you're asking about is to run your query adding on row
> numbers (pay attention to your ordering!), and then reference that result
> set from an update to get the primary key you want.  So I didn't test it,
> but something roughly like this:
>
> WITH tmp AS (SELECT X.field1, Y.field2,row_number() OVER () from X, Y
> WHERE X.id = Y.id ) UPDATE x SET ...  FROM tmp WHERE
>  tmp.row_number=5 AND x.field1=tmp.field1;
>

I didn't know that row_number() function exists and it is available across
different DBMSes.

I will test that query later.

Thank you.

Now one other little thing: could you point me to the documentation that
explains the meaning of the "window function"?



> Cheers,
> Ken
>
>
>
> --
> AGENCY Software
> A Free Software data system
> By and for non-profits
> *http://agency-software.org/ <http://agency-software.org/>*
> *https://demo.agency-software.org/client
> <https://demo.agency-software.org/client>*
> ken.tan...@agency-software.org
> (253) 245-3801
>
> Subscribe to the mailing list
>  to
> learn more about AGENCY or
> follow the discussion.
>

Can't query system tables during transaction

[Igor Korot]

Hi, ALL,
I'm trying to execute following:

SELECT 1 FROM pg_class c, pg_namespace n WHERE n.oid = c.relnamespace
AND c.relname = 'foo' AND n.nspname = public;

inside the transaction.

I'm getting the following error:

ERROR:  current transaction is aborted, commands ignored until end of
transaction block

Does this mean I can't query system tables during the transaction?
What is the problem here if it's not and how do I find out the reason?
And if it is - how to work around it?

I can probably commit it and start a new transaction, but I fear I will
have the same issue there...

Thank you.

If it matters - I'm working with C++ and libpq.

Re: Can't query system tables during transaction

[Igor Korot]

Hi,

On Sun, Oct 4, 2020 at 3:30 PM Tom Lane  wrote:
>
> Igor Korot  writes:
> > I'm trying to execute following:
>
> > SELECT 1 FROM pg_class c, pg_namespace n WHERE n.oid = c.relnamespace
> > AND c.relname = 'foo' AND n.nspname = public;
>
> I suppose you meant to put quotes around 'public'?

I suppose so as well. ;-)

>
> > I'm getting the following error:
> > ERROR:  current transaction is aborted, commands ignored until end of
> > transaction block
>
> This has nothing to do with the current command, but with failure
> of some previous command in the transaction.

Thank you.
I will try to track down the error.

>
> regards, tom lane

Failed to compile libpq

[Igor Korot]

Hi, ALL,
I just tried to compile libpq on latest Solaris x86 (12.4) with Solaris
compiler (Sollaris Studio) version 12.6.

Configure succeeded, but running "make" failed with

[quote]
make: Fatal error in reader ../../../src/Makefile.global, line 45:
Unexpected end of line seen
[/quote]

Is there a way to fix it?
Below is the complete log from the Terminal session:

[quote]
igor@solaris:~/dbhandlerSol/libpq$ ./configure --enable-debug
--enable-nls --with-openssl
checking build system type... i386-pc-solaris2.11
checking host system type... i386-pc-solaris2.11
checking which template to use... solaris
checking whether to build with 64-bit integer date/time support... yes
checking whether NLS is wanted... yes
checking for default port number... 5432
checking for block size... 8kB
checking for segment size... 1GB
checking for WAL block size... 8kB
checking for WAL segment size... 16MB
checking for gcc... no
checking for cc... cc
checking whether the C compiler works... yes
checking for C compiler default output file name... a.out
checking for suffix of executables...
checking whether we are cross compiling... no
checking for suffix of object files... o
checking whether we are using the GNU C compiler... no
checking whether cc accepts -g... yes
checking for cc option to accept ISO C89... none needed
checking whether the C compiler still works... yes
checking how to run the C preprocessor... cc -Xa -E
checking allow thread-safe client libraries... yes
checking whether to build with Tcl... no
checking whether to build Perl modules... no
checking whether to build Python modules... no
checking whether to build with GSSAPI support... no
checking whether to build with PAM support... no
checking whether to build with BSD Authentication support... no
checking whether to build with LDAP support... no
checking whether to build with Bonjour support... no
checking whether to build with OpenSSL support... yes
checking whether to build with SELinux support... no
checking whether to build with systemd support... no
checking for grep that handles long lines and -e... /usr/bin/ggrep
checking for egrep... /usr/bin/ggrep -E
checking for non-GNU ld... /usr/bin/ld
checking if the linker (/usr/bin/ld) is GNU ld... no
checking for ranlib... ranlib
checking for strip... strip
checking whether it is possible to strip libraries... no
checking for ar... ar
checking for a BSD-compatible install... /usr/bin/ginstall -c
checking for tar... /usr/bin/tar
checking whether ln -s works... yes
checking for gawk... gawk
checking for a thread-safe mkdir -p... /usr/bin/gmkdir -p
checking for bison... no
configure: WARNING:
*** Without Bison you will not be able to build PostgreSQL from Git nor
*** change any of the parser definition files.  You can obtain Bison from
*** a GNU mirror site.  (If you are using the official distribution of
*** PostgreSQL then you do not need to worry about this, because the Bison
*** output is pre-generated.)
checking for flex... no
configure: WARNING:
*** Without Flex you will not be able to build PostgreSQL from Git nor
*** change any of the scanner definition files.  You can obtain Flex from
*** a GNU mirror site.  (If you are using the official distribution of
*** PostgreSQL then you do not need to worry about this because the Flex
*** output is pre-generated.)
checking for perl... /usr/bin/perl
configure: using perl 5.22.1
checking for ANSI C header files... yes
checking for sys/types.h... yes
checking for sys/stat.h... yes
checking for stdlib.h... yes
checking for string.h... yes
checking for memory.h... yes
checking for strings.h... yes
checking for inttypes.h... yes
checking for stdint.h... yes
checking for unistd.h... yes
checking if compiler needs certain flags to reject unknown flags...
-Werror -Wunknown-warning-option -Wunused-command-line-argument
checking whether pthreads work with -mt -lpthread... yes
checking for joinable pthread attribute... PTHREAD_CREATE_JOINABLE
checking if more special flags are required for pthreads... -D_REENTRANT
checking for PTHREAD_PRIO_INHERIT... yes
checking pthread.h usability... yes
checking pthread.h presence... yes
checking for pthread.h... yes
checking for strerror_r... yes
checking for getpwuid_r... yes
checking for gethostbyname_r... yes
checking whether strerror_r returns int... yes
checking for main in -lm... yes
checking for library containing setproctitle... no
checking for library containing dlopen... none required
checking for library containing socket... none required
checking for library containing shl_load... no
checking for library containing getopt_long... none required
checking for library containing crypt... none required
checking for library containing shm_open... none required
checking for library containing shm_unlink... none required
checking for library containing fdatasync... none required
checking for library containing sched_yield... none required
checking for library containing gethostbyname_r... none required
checking for library containing shmg

Re: Failed to compile libpq

[Igor Korot]

Tom et al,

On Thu, Oct 15, 2020 at 12:01 AM Tom Lane  wrote:
>
> Igor Korot  writes:
> > Configure succeeded, but running "make" failed with
>
> > [quote]
> > make: Fatal error in reader ../../../src/Makefile.global, line 45:
> > Unexpected end of line seen
> > [/quote]
>
> Kinda looks like you're using some non-GNU make.

Correct.
It is from Solaris Studio compiler.

What should I do?

Thank you.

>
> regards, tom lane

Re: Failed to compile libpq

[Igor Korot]

Tom,

On Thu, Oct 15, 2020 at 12:16 AM Tom Lane  wrote:
>
> Igor Korot  writes:
> > On Thu, Oct 15, 2020 at 12:01 AM Tom Lane  wrote:
> >> Kinda looks like you're using some non-GNU make.
>
> > Correct.
> > It is from Solaris Studio compiler.
>
> > What should I do?
>
> Try "gmake".  If it's not already on the system, you'll need to
> install it.

Thx.
That's solved it.

>
> regards, tom lane

Attaching database

[Igor Korot]

Hi,
IIUC, PostgreSQL does not support attaching a database to an existing
connection.
However I was told that I can use this:
https://www.postgresql.org/docs/9.3/postgres-fdw.html.

Is it the same thing? Why there is no simple

ATTACH  AS 

?

Thank you.

Re: Attaching database

[Igor Korot]

Hi, Pavel,

On Mon, Oct 19, 2020 at 12:51 PM Pavel Stehule  wrote:
>
> Hi
>
> po 19. 10. 2020 v 19:40 odesílatel Igor Korot  napsal:
>>
>> Hi,
>> IIUC, PostgreSQL does not support attaching a database to an existing
>> connection.
>> However I was told that I can use this:
>> https://www.postgresql.org/docs/9.3/postgres-fdw.html.
>>
>> Is it the same thing? Why there is no simple
>>
>> ATTACH  AS 
>>
>
> It is a different thing - postgresql_fdw does nested connect - it uses 
> client-server protocol.
>
> For postgres connect and sql engine process is one entity - and engine is 
> written without a possibility to reconnect to another database.

So if I understand correctly the postgresql_fdw is creating a second
connection and uses it as kind of "virtualizing mechanism"
in order to give access to the second database.

Am I right?

Thank you.

>
> Regards
>
> Pavel
>
>
>
>>
>> ?
>>
>> Thank you.
>>
>>

Building for 64-bit platform

[Igor Korot]

Hi,
I build libpq with the standard configure/dmake.
Now I realize I need t build it for a 64-bit platform.

My questions are:
Is it enough to ust do

[code]
cd libpq
CFLAGS="-m64" LDFLAGS="-m64" ./configure
dmake
[/code]

or I have to do:

[code]
dmake clean
[/code]

?

2. Is my configure line above correct?
Or PostgreSQL configure contains special flags for 64 bit builds already?

Thank you.

Re: Building for 64-bit platform

[Igor Korot]

Thx.

On Sun, Nov 8, 2020 at 12:15 AM Tom Lane  wrote:
>
> Igor Korot  writes:
> > I build libpq with the standard configure/dmake.
> > Now I realize I need t build it for a 64-bit platform.
>
> > My questions are:
> > Is it enough to ust do
>
> Do "make distclean" at the top level, then re-configure with the
> new options and re-make.
>
> Maybe you can get away with a partial rebuild, but there is no way
> that it's worth your time to experiment.  On any machine built in
> the last two decades, you could have already finished a full rebuild
> in the time it took me to type this.  On the other hand, if you do
> a partial rebuild and it turns out to be broken, you could waste
> many hours figuring that out.
>
> regards, tom lane

Different bitness

[Igor Korot]

Hi, ALL,

[code]

CC -m64   -std=c++11 -o
dist/Debug/OracleDeveloperStudio-Solaris-x86/liblibpostgres.so
build/Debug/OracleDeveloperStudio-Solaris-x86/database_postgres.o
-L../postgresql/src/interfaces/libpq -lpq -m64 -std=c++11 -G -KPIC
-norunpath -h liblibpostgres.so
ld: fatal: file ../postgresql/src/interfaces/libpq/libpq.so: wrong ELF
class: ELFCLASS32
*** Error code 2
[/code]

However testing the library reveals:

[code]

igor@solaris:~/dbhandlerSol/libpq/src/interfaces/libpq$ file libpq.so
libpq.so:   ELF 64-bit LSB dynamic lib AMD64 Version 1 [SSE2 SSE],
dynamically linked, not stripped
[/code]

What is wrong here?

I did do the clean the default 32-bit build and then rebuild with:

[code]
CFLAGS="-m64" LDFLAGS="-m64" ./configure && cd src/interface/libpq && gmake
[/code]

What am I missing?

Thank you.

Re: Different bitness

[Igor Korot]

Thx, Tom.

On Sun, Nov 8, 2020 at 2:21 PM Tom Lane  wrote:
>
> Igor Korot  writes:
> > ld: fatal: file ../postgresql/src/interfaces/libpq/libpq.so: wrong ELF
> > class: ELFCLASS32
>
> That path doesn't seem to quite agree with this one:
>
> > igor@solaris:~/dbhandlerSol/libpq/src/interfaces/libpq$ file libpq.so
>
>
> regards, tom lane

Re: copy command - something not found

[Igor Korot]

Hi
You could try to do "set +x" before running the script...

Thank you.


On Tue, Dec 29, 2020, 2:23 PM David G. Johnston 
wrote:

> On Tue, Dec 29, 2020 at 1:01 PM Susan Hurst <
> susan.hu...@brookhurstdata.com> wrote:
>
>> Actually, the -c was in an example of a copy command that I found while
>> working at my last job. I tried executing the command without the -c and
>> got the same results as before, so I suppose I really don't know what it
>> means.
>>
>> Can you enlighten me
>>
> Read the fine manual before running stuff that you don't understand.
>
> https://www.postgresql.org/docs/current/app-psql.html
>
> Then, provide a self-contained script that demonstrates the problem
> because at this point I am either unable to follow or untrusting of the
> written description of the problem.
>
> Or consider using less indirection until you get something that is working
> and then add your layers incrementally testing as you go along.
>
> David J.
>
>

Get the table creation DDL

[Igor Korot]

Hi,
Is there a query I can execute that will give me CREATE TABLE() command
used to create a table?

Thank you.

Re: Get the table creation DDL

[Igor Korot]

Hi, Michael,

On Sun, Jul 10, 2022 at 11:13 AM Michael Nolan  wrote:
>
>
>
> On Sun, Jul 10, 2022 at 10:28 AM Igor Korot  wrote:
>>
>> Hi,
>> Is there a query I can execute that will give me CREATE TABLE() command
>> used to create a table?

So there is no "query" per se?
Also how do I pass the table name?

Thank you.

>>
>> Thank you.
>
>
> Use pg_dump --schema-only
> --
> Mike Nolan

Re: Get the table creation DDL

[Igor Korot]

Hi,

On Sun, Jul 10, 2022 at 11:47 AM Igor Korot  wrote:
>
> Hi, Michael,
>
> On Sun, Jul 10, 2022 at 11:13 AM Michael Nolan  wrote:
> >
> >
> >
> > On Sun, Jul 10, 2022 at 10:28 AM Igor Korot  wrote:
> >>
> >> Hi,
> >> Is there a query I can execute that will give me CREATE TABLE() command
> >> used to create a table?
>
> So there is no "query" per se?
> Also how do I pass the table name?
>
> Thank you.
>
> >>
> >> Thank you.
> >
> >
> > Use pg_dump --schema-only

In addition:

Can I send it to execute with PQexec() or SQLExecDirect()?

It is not a query, but an external command, so I'm wondering...

Thank you.

> > --
> > Mike Nolan

Re: Get the table creation DDL

[Igor Korot]

Hi,

On Sun, Jul 10, 2022 at 12:47 PM Ray O'Donnell  wrote:
>
> On 10/07/2022 17:47, Igor Korot wrote:
> > Hi, Michael,
> >
> > On Sun, Jul 10, 2022 at 11:13 AM Michael Nolan  wrote:
> >>
> >>
> >>
> >> On Sun, Jul 10, 2022 at 10:28 AM Igor Korot  wrote:
> >>>
> >>> Hi,
> >>> Is there a query I can execute that will give me CREATE TABLE() command
> >>> used to create a table?
> >
> > So there is no "query" per se?
> > Also how do I pass the table name?
> >
>
> If you connect to the database with psql including the -E option, then do
>
>  \d 

It means it is possible to have an actal query getting it...

Thank you.

>
> It will show you the SQL used to generate the output... this may help.
>
> Ray.
>
>
> --
> Raymond O'Donnell // Galway // Ireland
> r...@rodonnell.ie

Re: Get the table creation DDL

[Igor Korot]

Hi,

On Sun, Jul 10, 2022 at 2:27 PM Mladen Gogala  wrote:
>
> On 7/10/22 12:47, Igor Korot wrote:
>
> So there is no "query" per se?
> Also how do I pass the table name?
>
> Thank you.
>
> You can create one from the catalog tables. Personally, I would use 
> INFORMATION_SCHEMA to avoid pg_class and pg_attribute. However, there is an 
> extension which does that for you. Somebody else has already done the hard 
> work.

I understand.
The Problem is that I need to put this inside the C/ODBC interface for
my project.

I'm sure it is not a problem when people are working out of psql or
writing some scripts,
but for me it is painful to go and try to recreate it.

Now, I'm not sure if this extension can be freely re-used (query
extracted and placed
inside someone else's project).

Thank you.

>
> --
> Mladen Gogala
> Database Consultant
> Tel: (347) 321-1217
> https://dbwhisperer.wordpress.com

Re: Get the table creation DDL

[Igor Korot]

Hi,

On Sun, Jul 10, 2022 at 7:02 PM Mladen Gogala  wrote:
>
> On 7/10/22 17:00, Igor Korot wrote:
>
> I understand.
> The Problem is that I need to put this inside the C/ODBC interface for
> my project.
>
> I'm sure it is not a problem when people are working out of psql or
> writing some scripts,
> but for me it is painful to go and try to recreate it.
>
> Now, I'm not sure if this extension can be freely re-used (query
> extracted and placed
> inside someone else's project).
>
> Thank you.
>
> Igor, https://github.com/MichaelDBA/pg_get_tabledef provides 
> "pg_get_tabledef" function which can be called from SQL and therefore used 
> from ODBC/C. This "extension" is nothing PL/PGSQL source code of the function 
> that returns DDL. That's about it. This is how it works:

I understand.

The question here - does this "extension'' is a part of standard
PostgreSQL install?
And if not - can I copy and paste that code in my program?

Thank you.

>
> mgogala@umajor Downloads]$ psql -h postgres -f 
> pg_get_tabledef-main/pg_get_tabledef.sql
> Password for user mgogala:
> DO
> CREATE FUNCTION
> [mgogala@umajor Downloads]$ psql -h postgres
> Password for user mgogala:
> psql (13.6, server 14.4)
> WARNING: psql major version 13, server major version 14.
>  Some psql features might not work.
> Type "help" for help.
>
> mgogala=# select pg_get_tabledef('mgogala','emp');
>pg_get_tabledef
> -
>  CREATE  TABLE mgogala.emp (+
>empno smallint NOT NULL, +
>ename character varying(10) NULL,+
>job character varying(9) NULL,   +
>mgr smallint NULL,   +
>hiredate timestamp without time zone NULL,   +
>sal double precision NULL,   +
>comm double precision NULL,  +
>deptno smallint NULL,+
>CONSTRAINT emp_pkey PRIMARY KEY (empno), +
>CONSTRAINT fk_deptno FOREIGN KEY (deptno) REFERENCES dept(deptno)+
>  ) TABLESPACE pg_default;   +
> +
>
> (1 row)
>
> So, you clone the Git repository, run the "CREATE FUNCTION" script and, 
> voila, you can get the DDL for the desired table. Here is the same stuff 
> produced by the psql utility:
>
> mgogala=# \d emp
>Table "mgogala.emp"
>   Column  |Type | Collation | Nullable | Default
> --+-+---+--+-
>  empno| smallint|   | not null |
>  ename| character varying(10)   |   |  |
>  job  | character varying(9)|   |  |
>  mgr  | smallint|   |  |
>  hiredate | timestamp without time zone |   |  |
>  sal  | double precision|   |  |
>  comm | double precision|   |  |
>  deptno   | smallint|   |  |
> Indexes:
> "emp_pkey" PRIMARY KEY, btree (empno)
> Foreign-key constraints:
> "fk_deptno" FOREIGN KEY (deptno) REFERENCES dept(deptno)
>
> And here is using the function from an ODBC connection:
>
> [mgogala@umajor Downloads]$ isql mgogala-pg
> +---+
> | Connected!|
> |   |
> | sql-statement |
> | help [tablename]  |
> | quit  |
> |   |
> +---+
> SQL> select pg_get_tabledef('mgogala','emp');
> +-+
> | pg_get_tabledef 
>   
> 

Re: Get the table creation DDL

[Igor Korot]

Hi,

On Sun, Jul 10, 2022 at 8:09 PM Rob Sargent  wrote:
>
>
>
> > On Jul 10, 2022, at 6:16 PM, Igor Korot  wrote:
> >
> > Hi,
> >
> >> On Sun, Jul 10, 2022 at 7:02 PM Mladen Gogala  
> >> wrote:
> >>
> >> On 7/10/22 17:00, Igor Korot wrote:
> >>
> >> I understand.
> >> The Problem is that I need to put this inside the C/ODBC interface for
> >> my project.
> >>
> >> I'm sure it is not a problem when people are working out of psql or
> >> writing some scripts,
> >> but for me it is painful to go and try to recreate it.
> >>
> >> Now, I'm not sure if this extension can be freely re-used (query
> >> extracted and placed
> >> inside someone else's project).
> >>
> >> Thank you.
> >>
> >> Igor, https://github.com/MichaelDBA/pg_get_tabledef provides 
> >> "pg_get_tabledef" function which can be called from SQL and therefore used 
> >> from ODBC/C. This "extension" is nothing PL/PGSQL source code of the 
> >> function that returns DDL. That's about it. This is how it works:
> >
> > I understand.
> >
> > The question here - does this "extension'' is a part of standard
> > PostgreSQL install?
> > And if not - can I copy and paste that code in my program?
> >
> > Thank you.
> >
> >>
> >> mgogala@umajor Downloads]$ psql -h postgres -f 
> >> pg_get_tabledef-main/pg_get_tabledef.sql
> >> Password for user mgogala:
> >> DO
> >> CREATE FUNCTION
> >> [mgogala@umajor Downloads]$ psql -h postgres
> >> Password for user mgogala:
> >> psql (13.6, server 14.4)
> >> WARNING: psql major version 13, server major version 14.
> >> Some psql features might not work.
> >> Type "help" for help.
> >>
> >> mgogala=# select pg_get_tabledef('mgogala','emp');
> >>   pg_get_tabledef
> >> -
> >> CREATE  TABLE mgogala.emp (+
> >>   empno smallint NOT NULL, +
> >>   ename character varying(10) NULL,+
> >>   job character varying(9) NULL,   +
> >>   mgr smallint NULL,   +
> >>   hiredate timestamp without time zone NULL,   +
> >>   sal double precision NULL,   +
> >>   comm double precision NULL,  +
> >>   deptno smallint NULL,+
> >>   CONSTRAINT emp_pkey PRIMARY KEY (empno), +
> >>   CONSTRAINT fk_deptno FOREIGN KEY (deptno) REFERENCES dept(deptno)+
> >> ) TABLESPACE pg_default;   +
> >>+
> >>
> >> (1 row)
> >>
> >> So, you clone the Git repository, run the "CREATE FUNCTION" script and, 
> >> voila, you can get the DDL for the desired table. Here is the same stuff 
> >> produced by the psql utility:
> >>
> >> mgogala=# \d emp
> >>   Table "mgogala.emp"
> >>  Column  |Type | Collation | Nullable | Default
> >> --+-+---+--+-
> >> empno| smallint|   | not null |
> >> ename| character varying(10)   |   |  |
> >> job  | character varying(9)|   |  |
> >> mgr  | smallint|   |  |
> >> hiredate | timestamp without time zone |   |  |
> >> sal  | double precision|   |  |
> >> comm | double precision|   |  |
> >> deptno   | smallint|   |  |
> >> Indexes:
> >>"emp_pkey" PRIMARY KEY, btree (empno)
> >> Foreign-key constraints:
> >>"fk_deptno" FOREIGN KEY (deptno) REFERENCES dept

Re: Get the table creation DDL

[Igor Korot]

Hi,

On Sun, Jul 10, 2022 at 8:14 PM Mladen Gogala  wrote:
>
> On 7/10/22 21:13, Igor Korot wrote:
>
> How should I do that?
>
> Thank you.
>
> Oh boy! I give up.

Does he mean I need to make it as a GitHub module?
Can I even do that given that you use GitLab and my project is on GitHub?

Thank you.

>
> --
> Mladen Gogala
> Database Consultant
> Tel: (347) 321-1217
> https://dbwhisperer.wordpress.com

Re: Get the table creation DDL

[Igor Korot]

Hi,

On Mon, Jul 11, 2022 at 7:56 AM Rob Sargent  wrote:
>
> On 7/11/22 06:31, Mladen Gogala wrote:
> > On 7/10/22 22:52, Rob Sargent wrote:
> >> Are you alone or on a team?

No, this is my personal project.

Thank you.

> >
> > What are your pronouns?
> >
> This did make me chuckle, but no I am just asking whether or not the OP
> is currently part of a team.
>

Getting the table ID

[Igor Korot]

Hi, guys,

In the database theory each table is identified as "schema_name"."table_name".

When I tried to look at how to get the table id inside the PostgreSQL,
I saw that I needed to look at the pg_class table.

SELECT oid FROM pg_class WHERE relname = "table_name";

However that query will give a non-unique table id (see the first sentence).

So how do I get the table id based on the "schema_name.table_name"?

There is a pg_namespace table - is this where the schema should come from?
If yes - how?
Looking at that table I don't see any unique fields...
Or is this something that is hidden?

In fact I'm trying to run following query:

SELECT c.oid FROM pg_class c, pg_namespace nc WHERE nc.oid =
c.relnamespace AND c.relname = ? AND nc.nspname = ?;

from my ODBC based program, but it returns 0 rows on SQLFetch.

I know PostgreSQL does not use '?' for query parameters
but I thought that since its an ODBC everything should work.

Nevertheless, all bindings were successful, but now rows are returned.

Is this query correct?

Thank you.

Re: Getting the table ID

[Igor Korot]

Hi, guys,

On Tue, Jul 19, 2022 at 4:42 AM Walter Dörwald  wrote:
>
> On 19 Jul 2022, at 5:10, Igor Korot wrote:
>
> Hi, guys,
>
> In the database theory each table is identified as "schema_name"."table_name".
>
> When I tried to look at how to get the table id inside the PostgreSQL,
> I saw that I needed to look at the pg_class table.
>
> SELECT oid FROM pg_class WHERE relname = "table_name";
>
> However that query will give a non-unique table id (see the first sentence).
>
> So how do I get the table id based on the "schema_name.table_name"?
>
> There is a pg_namespace table - is this where the schema should come from?
> If yes - how?
> Looking at that table I don't see any unique fields...
> Or is this something that is hidden?
>
> In fact I'm trying to run following query:
>
> SELECT c.oid FROM pg_class c, pg_namespace nc WHERE nc.oid =
> c.relnamespace AND c.relname = ? AND nc.nspname = ?;
>
> from my ODBC based program, but it returns 0 rows on SQLFetch.
>
> I know PostgreSQL does not use '?' for query parameters
> but I thought that since its an ODBC everything should work.
>
> Nevertheless, all bindings were successful, but now rows are returned.
>
> Is this query correct?
>
> Thank you.
>
> That's more or less the same query that I am using:
>
> select
> r.oid as oid,
> n.nspname || '.' || r.relname as name
> from
> pg_catalog.pg_namespace n
> join
> pg_catalog.pg_class r on n.oid = r.relnamespace
> where
> (r.relkind = 'r') and
> (n.nspname not like 'pg_%') and
> (n.nspname != 'information_schema') and
> (n.nspname = 'email') and
> (r.relname = 'emailhistory')
>
> Maybe your problem has to to with uppercase/lowercase schema and/or table 
> names?

Below is my C++ code based on the ODBC library:

[code]
SQLHSTMT stmt = 0;
SQLHDBC hdbc;
SQLLEN cbName, cbTableName = SQL_NTS, cbSchemaName = SQL_NTS;
long id;
int result = 0;
std::wstring query;
SQLWCHAR *qry = NULL, *tname = NULL, *sname = NULL;;
query = L"SELECT c.oid FROM pg_class c, pg_namespace nc WHERE nc.oid =
c.relnamespace AND c.relname = ? AND nc.nspname = ?;";
qry = new SQLWCHAR[query.length() + 2];
tname = new SQLWCHAR[tableName.length() + 2];
sname = new SQLWCHAR[schemaName.length() + 2];
memset( tname, '\0', tableName.length() + 2 );
memset( sname, '\0', schemaName.length() + 2);
uc_to_str_cpy( sname, schemaName );
uc_to_str_cpy( tname, tableName );
memset( qry, '\0', query.length() + 2 );
uc_to_str_cpy( qry, query );
SQLRETURN retcode = SQLAllocHandle( SQL_HANDLE_DBC, m_env, &hdbc );
if( retcode != SQL_SUCCESS && retcode != SQL_SUCCESS_WITH_INFO )
{
GetErrorMessage( errorMsg, 0 );
result = 1;
}
else
{
SQLSMALLINT OutConnStrLen;
retcode = SQLDriverConnect( hdbc, NULL, m_connectString, SQL_NTS,
NULL, 0, &OutConnStrLen, SQL_DRIVER_NOPROMPT );
if( retcode != SQL_SUCCESS && retcode != SQL_SUCCESS_WITH_INFO )
{
GetErrorMessage( errorMsg, 2, hdbc );
result = 1;
}
else
{
auto dbName = new SQLWCHAR[pimpl->m_dbName.length() + 2];
memset( dbName, '\0', pimpl->m_dbName.length() + 2 );
uc_to_str_cpy( dbName, pimpl->m_dbName );
retcode = SQLSetConnectAttr( hdbc, SQL_ATTR_CURRENT_CATALOG, dbName, SQL_NTS );
delete[] dbName;
dbName = nullptr;
if( retcode != SQL_SUCCESS && retcode != SQL_SUCCESS_WITH_INFO )
{
GetErrorMessage( errorMsg, 2 );
result = 1;
retcode = SQLEndTran( SQL_HANDLE_DBC, m_hdbc, SQL_ROLLBACK );
}
else
{
retcode = SQLAllocHandle( SQL_HANDLE_STMT, hdbc, &stmt );
if( retcode != SQL_SUCCESS && retcode != SQL_SUCCESS_WITH_INFO )
{
GetErrorMessage( errorMsg, 2, hdbc );
result = 1;
}
else
{
retcode = SQLPrepare( stmt, qry, SQL_NTS );
if( retcode != SQL_SUCCESS && retcode != SQL_SUCCESS_WITH_INFO )
{
GetErrorMessage( errorMsg, 1, stmt );
result = 1;
}
else
{
SQLSMALLINT dataType[2], decimalDigit[2], nullable[2];
SQLULEN parameterSize[2];
retcode = SQLDescribeParam( stmt, 1, &dataType[0], ¶meterSize[0],
&decimalDigit[0], &nullable[0] );
if( retcode != SQL_SUCCESS && retcode != SQL_SUCCESS_WITH_INFO )
{
GetErrorMessage( errorMsg, 1, stmt );
result = 1;
}
else
{
retcode = SQLBindParameter( stmt, 1, SQL_PARAM_INPUT, SQL_C_DEFAULT,
dataType[0], parameterSize[0], decimalDigit[0], tname, 0, &cbTableName
);
if( retcode != SQL_SUCCESS && retcode != SQL_SUCCESS_WITH_INFO )
{
GetErrorMessage( errorMsg, 1, stmt );
result = 1;
}
}
if( !result )
{
retcode = SQLDescribeParam( stmt, 2, &dataType[1], ¶meterSize[1],
&decimalDigit[1], &nullable[1] );
if( retcode != SQL_SUCCESS && retcode != SQL_SUCCESS_WITH_INFO )
{
GetErrorMessage( errorMsg, 1, stmt );
result = 1;
}
else
{
retcode = SQLBindParameter( stmt, 2, S

Logging the query executed on the server

[Igor Korot]

Hi,
Is it possible to log the query that will be executed
on the server?

I'm writing an application that connects to the server
through ODBC and libpq.
For some reason ODBC interface is failing - it desn't
return any rows

So I'm thinking if I have a proof that the query I am
actually executing is the same as the one I run through
the psql - I will know where to look.

I am actually binding some parameters and trying to
execute the query.

Thank you.

Re: Logging the query executed on the server

[Igor Korot]

Hi, Steve,

On Sun, Jul 24, 2022 at 12:51 AM Steve Baldwin  wrote:
>
> Hi Igor,
>
> Before you issue your query, try something like this:
>
> (from psql, but hopefully you get the idea)
>
> b2bcreditonline=# set log_min_duration_statement to 0;
> SET
> b2bcreditonline=# set log_statement to 'all';
> SET
>
> Ref: https://www.postgresql.org/docs/current/sql-set.html, 
> https://www.postgresql.org/docs/current/config-setting.html
>
> Then submit your query and it will be set to the server log. You can get the 
> name of the current logfile with:
>
> b2bcreditonline=# select pg_current_logfile();
>  pg_current_logfile
> 
>  /log/pg.csv

2 things:
1. How do I turn this off? ;-)
2. The log does show the query but it shows it with the placeholders.
Is there a way to see the actual query?

Thank you.


>
> HTH,
>
> Steve
>
> On Sun, Jul 24, 2022 at 3:26 PM Igor Korot  wrote:
>>
>> Hi,
>> Is it possible to log the query that will be executed
>> on the server?
>>
>> I'm writing an application that connects to the server
>> through ODBC and libpq.
>> For some reason ODBC interface is failing - it desn't
>> return any rows
>>
>> So I'm thinking if I have a proof that the query I am
>> actually executing is the same as the one I run through
>> the psql - I will know where to look.
>>
>> I am actually binding some parameters and trying to
>> execute the query.
>>
>> Thank you.
>>
>>

Is ODBC list still alive?

[Igor Korot]

Thank you.

Is ODBC list dead?

[Igor Korot]

Hi,
If the list IS dead - where can I get help on the topic...

Thank you.

Is there Postgres ODBC binary for OSX?

[Igor Korot]

Hi, ALL,
Is building it myself the only option?

Thank you.

Re: Is there Postgres ODBC binary for OSX?

[Igor Korot]

Hi,

On Fri, Sep 30, 2022 at 10:41 AM Daniel Gustafsson  wrote:
>
> > On 30 Sep 2022, at 17:36, Igor Korot  wrote:
>
> > Is building it myself the only option?
>
> A quick googling indicates that there are options in the macOS package
> managers:
>
> https://formulae.brew.sh/formula/psqlodbc#default

Thx for the reply.

I don't have a brew installed.
I was hoping to have a download of the dmg  and just run it
on my Mac. The one that is produced by the PG devs...

Guess I was too hopeful...

Thank you.

>
> --
> Daniel Gustafsson   https://vmware.com/
>

Re: Attaching database

[Igor Korot]

Hi, guys,

On Sun, Oct 25, 2020 at 1:27 PM Peter J. Holzer  wrote:
>
> On 2020-10-19 20:21:05 +0200, Pavel Stehule wrote:
> > po 19. 10. 2020 v 20:18 odesílatel Igor Korot  napsal:
> > On Mon, Oct 19, 2020 at 12:51 PM Pavel Stehule 
> > wrote:
> > > It is a different thing - postgresql_fdw does nested connect - it uses
> > > client-server protocol.
> > >
> > > For postgres connect and sql engine process is one entity - and 
> > engine is
> > > written without a possibility to reconnect to another database.
> >
> > So if I understand correctly the postgresql_fdw is creating a second
> > connection and uses it as kind of "virtualizing mechanism"
> > in order to give access to the second database.
> >
> >
> > yes - it does new connect to somewhere (local or remote server, but 
> > mechanism
> > is absolutely same)
>
> In case this isn't clear:
>
> It is the server (or more specifically, the foreign data wrapper) which
> opens that connection. To the client it looks like it's just accessing a
> normal table within the same database.

Sorry for resurrecting this old thread...
If an attaching the DB creates new connection which will be cmpletely
independent - how the INFORMATION_SCHEMA.table@table_catalog
field is handled.

Lets say I open connection to the DB (finance) and then attached another DB
(finance_2021).

So, when I call SELECT table_schema, table_name FROM INFORMATION_SCHEMA.table
I will get all tables from (finance) DB only.
And to get all tables from (finance_2021) I need to make this catalog current
and reissue the query.

Am I right?

Thank you.

>
> hp
>
> --
>_  | Peter J. Holzer| Story must make more sense than reality.
> |_|_) ||
> | |   | h...@hjp.at |-- Charles Stross, "Creative writing
> __/   | http://www.hjp.at/ |   challenge!"

Re: Attaching database

[Igor Korot]

Hi, David et al,

On Fri, Oct 14, 2022 at 11:39 PM David G. Johnston
 wrote:
>
> On Fri, Oct 14, 2022 at 9:17 PM Igor Korot  wrote:
>>
>> Hi, guys,
>>
>> On Sun, Oct 25, 2020 at 1:27 PM Peter J. Holzer  wrote:
>> >
>> > On 2020-10-19 20:21:05 +0200, Pavel Stehule wrote:
>>
>> > In case this isn't clear:
>> >
>> > It is the server (or more specifically, the foreign data wrapper) which
>> > opens that connection. To the client it looks like it's just accessing a
>> > normal table within the same database.
>>
>> Sorry for resurrecting this old thread...
>
>
> Then why did you do it?  You couldn't send a new email without copying 
> possibly no longer interested people and with better thought out 
> self-contained content that simply notes you are somehow using an FDW.
>
>>
>> If an attaching the DB creates new connection which will be cmpletely
>> independent - how the INFORMATION_SCHEMA.table@table_catalog
>> field is handled.
>>
>> Lets say I open connection to the DB (finance) and then attached another DB
>> (finance_2021).
>>
>> So, when I call SELECT table_schema, table_name FROM INFORMATION_SCHEMA.table
>
>
> Call this how exactly? There are three information_schema instances that you 
> can be talking about, though only the one in the local database is going to 
> be called that.  If you are dealing with FDWs you would have to have 
> different names involved.
>
>>
>> I will get all tables from (finance) DB only.
>> And to get all tables from (finance_2021) I need to make this catalog current
>> and reissue the query.
>>
>> Am I right?
>
>
> Do it and find out?  Then if still confused, show what you attempted.  But I 
> don't know what this concept of "make the catalog current" you speak of comes 
> from.  That isn't a thing that I am aware of.  Where do you see this 
> documented?

Making catalog current means switching between DBs.
Remember initially I connected to (finance) DB, which made the (finance) catalog
current.
Then I "opened a second connection" to (finance_2021), which made
that current catalog and so that select would give me all tables from
(finance_2021).

I hope now its clearer.

Thank you.

>
> David J.

Re: Attaching database

[Igor Korot]

Hi, Adrian,



On Sat, Oct 15, 2022, 10:51 AM Adrian Klaver 
wrote:

> On 10/15/22 08:20, Adrian Klaver wrote:
> > On 10/14/22 21:46, Igor Korot wrote:
> >
> >
> >> Making catalog current means switching between DBs.
> >> Remember initially I connected to (finance) DB, which made the
> >> (finance) catalog
> >> current.
> >> Then I "opened a second connection" to (finance_2021), which made
> >> that current catalog and so that select would give me all tables from
> >> (finance_2021).
> >
> > No you didn't.
> >
> > Rough outline of what happens:
> >
> > 1) You connected to the database finance.
> > 2) While in the finance database you queried the foreign tables that are
> > linked to finance_2021.
> > 3) The queries made connections the  to finance_2021 for the purposes of
> > making the data visible in the foreign tables in finance.
> > 4) The client you did this did not 'leave' the finance database, so the
>  ^
>  in
> > only information_schema you have access to is in the finance database.
>

So any and all operations/queries performed before, during or after that
will be done on (finance) catalog), because this is the "main" connection,
right?

Thank you.

>
> >>
> >> I hope now its clearer.
> >>
> >> Thank you.
> >>
> >>>
> >>> David J.
> >>
> >>
> >
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>
>

Re: Attaching database

[Igor Korot]

Hi, guys,
After reading the documentation on
https://www.postgresql.org/docs/current/postgres-fdw.html
and checking the example I have a different question.

The presentation in the link referenced doesn't explain how to get the
table list on the
remote server and the information on the specific table.

The example tals about connection and creating a linked table, but it
doesn't explain
how to know the information about the tables on the remote

Is it possible or I will have to know this beforehand?

(Sorry for the top-posting).

Thank you.


On Sat, Oct 15, 2022 at 5:57 PM Alex Theodossis  wrote:
>
> Hi,
>
> you can only run inquires now on information_schema for the database you
> are connected to.
>
> If you are looking/searching in pg_database though, you can information
> globally.
>
> Something changed recently (I am using Dbeaver); I was able to attach to
> a single database and see all my databases; run queries, etc.
>
> With the latest update, now you have to have a separate connection to
> each database.
>
> Navicat now does the same thing, so my thinking is they must have
> changed the drivers.
>
> Regards,
>
> On 10/15/22 02:06, Julien Rouhaud wrote:
> > Hi,
> >
> > On Fri, Oct 14, 2022 at 11:16:44PM -0500, Igor Korot wrote:
> >> Sorry for resurrecting this old thread...
> >> If an attaching the DB creates new connection which will be cmpletely
> >> independent - how the INFORMATION_SCHEMA.table@table_catalog
> >> field is handled.
> >>
> >> Lets say I open connection to the DB (finance) and then attached another DB
> >> (finance_2021).
> >>
> >> So, when I call SELECT table_schema, table_name FROM 
> >> INFORMATION_SCHEMA.table
> >> I will get all tables from (finance) DB only.
> >> And to get all tables from (finance_2021) I need to make this catalog 
> >> current
> >> and reissue the query.
> >>
> >> Am I right?
> > No.  In postgres, databases are completely disjoint containers and once you
> > have a connection on a given database it will stay on that database, 
> > there's no
> > way around that.
> >
> > Using postgres_fdw allows you to create a local table that will point to
> > another table, possibly on another database or even another server, but it 
> > will
> > still be a (foreign) table, that has to be created in the current database 
> > in
> > the schema of your choice.
> >
> > Depending on your use case, maybe what you could do is create a finance_2021
> > schema, and create all the foreign tables you need in that schema pointing 
> > to
> > the finance_2021 database.  Any table existing in both "finance" and
> > "finance_2021" will then be visible in information_schema.tables, with a
> > different table_schema.  If you have multiple schemas in each database, then
> > find a way to make it work, maybe adding a _2021 suffix on each schema or
> > something like that.
> >
> > You can then maybe use the search_path (see
> > https://www.postgresql.org/docs/current/ddl-schemas.html#DDL-SCHEMAS-PATH) 
> > to
> > use by default one of the set of tables rather than the other.  But in any
> > case, it's only a workaround that has to be implemented on your client, as 
> > you
> > will always be connected on the same database, and see both set of object in
> > information_schema.
> >
> >
> --
> Alex Theodossis
> a...@dossi.info
> 347-514-5420
>
>
>

List user databases

[Igor Korot]

Hi, ALL,
According to 
https://chartio.com/resources/tutorials/how-to-list-databases-and-tables-in-postgresql-using-psql/,
there are generally 3 system DBs.

However I'm confused with the word general.
How many system databases can be made on a PG server?

Thank you.

Printf-like function

[Igor Korot]

Hi, ALL,
Is there a printf-lilke function inside libpq that can be used
to create a proper query string with the proper quoting of the
literal?

Or I will need to use sprintf()?

Thank you.

Re: Printf-like function

[Igor Korot]

Thank you.

On Fri, Nov 11, 2022 at 11:12 PM Tom Lane  wrote:
>
> Igor Korot  writes:
> > Is there a printf-lilke function inside libpq that can be used
> > to create a proper query string with the proper quoting of the
> > literal?
>
> No.  You'd be better off to transmit the literal value as a
> parameter.
>
> regards, tom lane

Get table catalog from pg_indexes

[Igor Korot]

Hi, ALL,
Table pg_indexes does not contain a field for a catalog.

So how do I get that?

SELECT 1 FROM pg_indexes WHERE indexname = $1 AND tablename = $2 AND
schemaname = $3

Thank you.

Re: Get table catalog from pg_indexes

[Igor Korot]

Hi, Erik,

On Sun, Nov 27, 2022 at 11:59 AM Erik Wienhold  wrote:
>
> > On 27/11/2022 18:22 CET Igor Korot  wrote:
> >
> > Table pg_indexes does not contain a field for a catalog.
> >
> > So how do I get that?
> >
> > SELECT 1 FROM pg_indexes WHERE indexname = $1 AND tablename = $2 AND
> > schemaname = $3
>
> Use SELECT current_database() if you need to know the catalog.
> pg_indexes only covers the current database[1].

>From the lin yo referenced:

[quote]
The view pg_indexes provides access to useful information about each
index in the database.
[/quote]

It doesn't say anything about "current" DB - only the DB.

However, I think I can try "SELECT 1 FROM .pg_indexes...".
Will this work?

Thank you.

>
> [1] https://www.postgresql.org/docs/current/view-pg-indexes.html
>
> --
> Erik

Re: Get table catalog from pg_indexes

[Igor Korot]

Hi, Christopher,

On Sun, Nov 27, 2022 at 12:45 PM Christophe Pettus  wrote:
>
>
>
> > On Nov 27, 2022, at 10:42, Igor Korot  wrote:
> >
> > It doesn't say anything about "current" DB - only the DB.
>
> In PostgreSQL terminology, a single PostgreSQL instance (the cluster) can 
> have multiple databases.

Just like any other DBMS.

> The database the connection is currently open to is the current database.

Are you saying I can't run any query on other DB? Or connect to DB and
run select?

Thank you.

Re: Get table catalog from pg_indexes

[Igor Korot]

Thx, Adrian.

On Sun, Nov 27, 2022 at 3:56 PM Adrian Klaver  wrote:
>
> On 11/27/22 13:31, Ron wrote:
> > On 11/27/22 11:22, Igor Korot wrote:
> >> Hi, ALL,
> >> Table pg_indexes does not contain a field for a catalog.
> >>
> >> So how do I get that?
> >>
> >> SELECT 1 FROM pg_indexes WHERE indexname = $1 AND tablename = $2 AND
> >> schemaname = $3
> >
> > You did not look hard enough, or Google "postgresql pg_indexes".
> >
> > test=# \d pg_indexes
> >  View "pg_catalog.pg_indexes"
> > Column   | Type | Collation | Nullable | Default
> > +--+---+--+-
> > *schemaname* | name | |  |
> > *tablename*  | name | |  |
> > *indexname*  | name | |  |
> >   tablespace | name |   |  |
> >   indexdef   | text |   |  |
> >
> > https://www.postgresql.org/docs/current/view-pg-indexes.html
>
> What the OP was looking for a field in the above that was catalogname or
> datname per:
>
> https://www.postgresql.org/docs/current/catalog-pg-database.html
>
> Table "pg_catalog.pg_database"
>  Column |   Type| Collation | Nullable | Default
> ---+---+---+--+-
>   oid   | oid   |   | not null |
>   datname   | name  |   | not null |
> ...
>
> In other words to filter the pg_index results by database/catalog name.
> Since pg_index is scoped to the database you are in when you do the
> query that is not going to happen.
>
> >
> >
> >
> >
> > --
> > Angular momentum makes the world go 'round.
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>
>
>

Re: Get table catalog from pg_indexes

[Igor Korot]

David,

On Sun, Nov 27, 2022 at 12:55 PM David G. Johnston
 wrote:
>
> On Sun, Nov 27, 2022 at 11:42 AM Igor Korot  wrote:
>>
>>
>> It doesn't say anything about "current" DB - only the DB.
>
>
> Yes, but you must be connected to some database in order to execute this 
> command: "the database" refers to this database you are connected to.

Yes, I am and I get that.

>
> The catalogs are not information_schema.
>
>>
>> However, I think I can try "SELECT 1 FROM .pg_indexes...".
>> Will this work?
>>
>
> What is that even supposed to mean?  It also seems simple enough to just do 
> that asking "will this work" is a waste of time.  Just try it.

Apparently it looks like this query fails to execute.
I am connected to the "draft" database and running

SELECT 1 FROM draft.pg_indexes;

gives:

[quote]
ERROR:schema "draft" does not exist
[/quote]

Thank you/

>
> David J.

Re: Get table catalog from pg_indexes

[Igor Korot]

Ron,

On Sun, Nov 27, 2022 at 4:10 PM Ron  wrote:
>
> On 11/27/22 15:55, Adrian Klaver wrote:
> > On 11/27/22 13:31, Ron wrote:
> >> On 11/27/22 11:22, Igor Korot wrote:
> >>> Hi, ALL,
> >>> Table pg_indexes does not contain a field for a catalog.
> >>>
> >>> So how do I get that?
> >>>
> >>> SELECT 1 FROM pg_indexes WHERE indexname = $1 AND tablename = $2 AND
> >>> schemaname = $3
> >>
> >> You did not look hard enough, or Google "postgresql pg_indexes".
> >>
> >> test=# \d pg_indexes
> >>  View "pg_catalog.pg_indexes"
> >> Column   | Type | Collation | Nullable | Default
> >> +--+---+--+-
> >> *schemaname* | name | |  |
> >> *tablename*  | name | |  |
> >> *indexname*  | name | |  |
> >>   tablespace | name |   |  |
> >>   indexdef   | text |   |  |
> >>
> >> https://www.postgresql.org/docs/current/view-pg-indexes.html
> >
> > What the OP was looking for a field in the above that was catalogname or
> > datname per:
>
> I've never heard of a database referred to as a catalog.  (That's always
> been where a database's metadata -- i.e. the pg_catalog schema -- is stored.)

In the ODBC terminology the DB is usually referenced as catalog.

Thank you.

>
> --
> Angular momentum makes the world go 'round.
>
>

Fwd: Log file

[Igor Korot]

I initially sent this to the ODBC list.'

Thank you for any suggestions.

-- Forwarded message -
From: Igor Korot 
Date: Sun, Oct 28, 2018 at 11:03 PM
Subject: Log file
To: PostgreSQL ODBC list 


Hi, ALL,
I'm trying to test the functionality of logging on my older Mac with
PostgreSQL 9.1.

I see that the logile is created with the owner of postgres and the
group of wheel.

Is there a way to make it open with ""?
Or I will have to change the owner/group manuall every time I will
access the file?

Thank you.

Re: Fwd: Log file

[Igor Korot]

Hi, Tom,

On Mon, Oct 29, 2018 at 1:56 PM Tom Lane  wrote:
>
> Igor Korot  writes:
> > I'm trying to test the functionality of logging on my older Mac with
> > PostgreSQL 9.1.
> > I see that the logile is created with the owner of postgres and the
> > group of wheel.
>
> Well, more specifically, it's created under the OS user & group that
> the server is running under.

OK, that clarifies it a little.
I thought I could just crate a user called "igor", give him all
"postgres" permissions
and login to the server as "igor" and not "postgres" every time I test.
But since the server will probably run from the "postgres" account
during the machine
start-up that won't work.

>
> > Is there a way to make it open with ""?
>
> What current user?  The SQL user name might not correspond to any
> OS-level entity at all.  Even if it did, it's quite unlikely that
> the OS would permit the server process to create files owned by
> some other OS user --- doing so would be a giant security risk.

The current_user = user who logged in to the machine and open the
current session.
If I log in to the machine as "igor" and try to create a file in vi/nano/notepad
I will be the owner of this file and the group will be the group to which
I belong as a user.

And I'm talking about specifically to the result of the "ls -la"
output from the *nix/OSX
POV.

>
> > Or I will have to change the owner/group manuall every time I will
> > access the file?
>
> You can set up the log files as readable by the OS group of the server
> (see log_file_mode), and then grant membership in that group to whichever
> OS accounts you trust.  You may also need to move the log directory
> out from under $PGDATA to make that work, since PG doesn't like
> world-readable data directories.

I'm trying to make the log file of PG readable of the user who logs in
to the current
OS session. I don't need a write permission, just read.
Because my program will not be started from the "postgres" account.

Thank you.

>
> regards, tom lane

Re: Fwd: Log file

[Igor Korot]

Hi, Tom,

On Mon, Oct 29, 2018 at 5:08 PM Tom Lane  wrote:
>
> Igor Korot  writes:
> > On Mon, Oct 29, 2018 at 1:56 PM Tom Lane  wrote:
> >> You can set up the log files as readable by the OS group of the server
> >> (see log_file_mode), and then grant membership in that group to whichever
> >> OS accounts you trust.  You may also need to move the log directory
> >> out from under $PGDATA to make that work, since PG doesn't like
> >> world-readable data directories.
>
> > I'm trying to make the log file of PG readable of the user who logs in
> > to the current
> > OS session. I don't need a write permission, just read.
> > Because my program will not be started from the "postgres" account.
>
> Well, any such setup is a serious security hole in itself, because
> there is likely to be sensitive data in the postmaster log, eg
> passwords.  (Remember that the log file is global to the whole cluster,
> it will not contain just data relevant to the current session.)
> You should only grant access to people who you trust at more or less
> the level of trust you'd put in the installation DBA.
>
> It may be that these concerns are all irrelevant to you because it's
> a single-user installation anyway, but they're not irrelevant to
> people running multi-user installations.  So that's why you can't
> get Postgres to do it.  In a single-user installation, maybe you
> should just launch the postmaster as that user.
>
> regards, tom lane

OK, I understand.

Thank you.

Re: Fwd: Log file

[Igor Korot]

Now is there a command to flush the log - delete the content of it?

All I'm looking for in the log are DDL commands - CREATE/ALTER/DELETE ones.


On Wed, Oct 31, 2018 at 12:32 AM Igor Korot  wrote:
>
> Hi, Tom,
>
> On Mon, Oct 29, 2018 at 5:08 PM Tom Lane  wrote:
> >
> > Igor Korot  writes:
> > > On Mon, Oct 29, 2018 at 1:56 PM Tom Lane  wrote:
> > >> You can set up the log files as readable by the OS group of the server
> > >> (see log_file_mode), and then grant membership in that group to whichever
> > >> OS accounts you trust.  You may also need to move the log directory
> > >> out from under $PGDATA to make that work, since PG doesn't like
> > >> world-readable data directories.
> >
> > > I'm trying to make the log file of PG readable of the user who logs in
> > > to the current
> > > OS session. I don't need a write permission, just read.
> > > Because my program will not be started from the "postgres" account.
> >
> > Well, any such setup is a serious security hole in itself, because
> > there is likely to be sensitive data in the postmaster log, eg
> > passwords.  (Remember that the log file is global to the whole cluster,
> > it will not contain just data relevant to the current session.)
> > You should only grant access to people who you trust at more or less
> > the level of trust you'd put in the installation DBA.
> >
> > It may be that these concerns are all irrelevant to you because it's
> > a single-user installation anyway, but they're not irrelevant to
> > people running multi-user installations.  So that's why you can't
> > get Postgres to do it.  In a single-user installation, maybe you
> > should just launch the postmaster as that user.
> >
> > regards, tom lane
>
> OK, I understand.
>
> Thank you.

Re: Fwd: Log file

[Igor Korot]

Hi, guys,
For some reason this reply was in my "Spam" folder (gmail service).
I guess moving to the new mailer service is not completely safe for "Spam".

So replying now - apologies to be late.

On Wed, Oct 31, 2018 at 2:00 AM Laurenz Albe  wrote:
>
> Igor Korot wrote:
> > Now is there a command to flush the log - delete the content of it?
>
> No, managing the logs is outside of PostgreSQL's responsibility.
>
> But it shouldn't be a problem to do this outside the database.
> Of course you could write a funtion in PostgreSQL that uses one
> of the "untrusted" procedural languages to do it for you.

Ok.
I guess I will have to write such function.

>
> > All I'm looking for in the log are DDL commands - CREATE/ALTER/DELETE ones.
>
> You mean DROP, right?

Yes, sorry.

>
> You can set "log_statement = 'ddl'" for that.

That's what I did.
But now I need to find a way to read the log file by the regular user
and not the "postgres" one.

Thank you.

>
> Yours,
> Laurenz Albe
> --
> Cybertec | https://www.cybertec-postgresql.com
>

Re: How to watch for schema changes

[Igor Korot]

Hi, Adrian,
Sorry for the delay to come back to this. I was busy doing other things.

On Tue, Jul 3, 2018 at 12:32 PM Adrian Klaver  wrote:
>
> On 07/03/2018 10:21 AM, Igor Korot wrote:
> > Hi, ALL,
> > Is there any trigger or some other means I can do on the server
> > which will watch for CREATE/ALTER/DROP TABLE command and after successful
> > execution of those will issue a NOTIFY statement?
>
> https://www.postgresql.org/docs/10/static/event-triggers.html

So if I understand correctly, I should write the trigger for the event
I am interested in.
And in this trigger I write a little SQL that will write the DDL
command in some temporary table.

I'm just looking for a way to execute this trigger and a function from
my C++ code
on the connection (either ODBC or thru the libpq).

And then in my C++ code I will continuously query this temporary table.

Or there is a better alternative on the algorithm?

Thank you.

>> >
> > Thank you.
> >
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com

Re: How to watch for schema changes

[Igor Korot]

Hi, Adrian,

On Mon, Dec 3, 2018 at 9:17 AM Adrian Klaver  wrote:
>
> On 12/2/18 5:24 AM, Igor Korot wrote:
> > Hi, Adrian,
> > Sorry for the delay to come back to this. I was busy doing other things.
> >
> > On Tue, Jul 3, 2018 at 12:32 PM Adrian Klaver  
> > wrote:
> >>
> >> On 07/03/2018 10:21 AM, Igor Korot wrote:
> >>> Hi, ALL,
> >>> Is there any trigger or some other means I can do on the server
> >>> which will watch for CREATE/ALTER/DROP TABLE command and after successful
> >>> execution of those will issue a NOTIFY statement?
> >>
> >> https://www.postgresql.org/docs/10/static/event-triggers.html
> >
> > So if I understand correctly, I should write the trigger for the event
> > I am interested in.
> > And in this trigger I write a little SQL that will write the DDL
> > command in some temporary table.
> >
> > I'm just looking for a way to execute this trigger and a function from
> > my C++ code
> > on the connection (either ODBC or thru the libpq).
>
> Event triggers are fired by database events not by external prompting,
> so you do not need to have your code execute the trigger. You do have
> the option of disabling/enabling then though:
>
> https://www.postgresql.org/docs/10/sql-altereventtrigger.html
.
Yes, but the code to the event triogger needs to be written and then executed on
connection, right?

So, this is what I'm thingking:

[code]
ret = SQLExecute( m_hstmt, L"CREATE FUNCTION schema_watch_check()
RETURNS event_trigger AS schema_watch LANGUAGE C", SQL_NTS );
if( ret == SQL_SUCCESS || ret== SQL_SUCCESS_WITH_INFO )
{
ret = SQLExecute( m_hstmt, L"CREATE EVENT TRIGGER schema_watch ON
ddl_command_end EXECUTE PROCEDURE schema_watch_check()", SQL_NTS );
}
[/code]

and something to that extent on the libpq connection.

Am I missing something here?

Now the other question is - how to make it work?
I can write the function code, compile it and place in the folder
where my executable is (or it should be in some postgreSQL dir?) and
then executing above code
will be enough. Is this correct?

>
> >
> > And then in my C++ code I will continuously query this temporary table.
>
> Why a temporary table? They are session specific and if the session
> aborts prematurely you will lose the information.

Is there a better alternative?

Thank you.

>
> >
> > Or there is a better alternative on the algorithm?
> >
> > Thank you.
> >
> >>>>
> >>> Thank you.
> >>>
> >>
> >>
> >> --
> >> Adrian Klaver
> >> adrian.kla...@aklaver.com
> >
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com

Re: How to watch for schema changes

[Igor Korot]

On Mon, Dec 3, 2018 at 11:29 AM Adrian Klaver  wrote:
>
> On 12/3/18 8:16 AM, Igor Korot wrote:
> > Hi, Adrian,
> >
> > On Mon, Dec 3, 2018 at 9:17 AM Adrian Klaver  
> > wrote:
> >>
> >> On 12/2/18 5:24 AM, Igor Korot wrote:
> >>> Hi, Adrian,
> >>> Sorry for the delay to come back to this. I was busy doing other things.
> >>>
> >>> On Tue, Jul 3, 2018 at 12:32 PM Adrian Klaver  
> >>> wrote:
> >>>>
> >>>> On 07/03/2018 10:21 AM, Igor Korot wrote:
> >>>>> Hi, ALL,
> >>>>> Is there any trigger or some other means I can do on the server
> >>>>> which will watch for CREATE/ALTER/DROP TABLE command and after 
> >>>>> successful
> >>>>> execution of those will issue a NOTIFY statement?
> >>>>
> >>>> https://www.postgresql.org/docs/10/static/event-triggers.html
> >>>
> >>> So if I understand correctly, I should write the trigger for the event
> >>> I am interested in.
> >>> And in this trigger I write a little SQL that will write the DDL
> >>> command in some temporary table.
> >>>
> >>> I'm just looking for a way to execute this trigger and a function from
> >>> my C++ code
> >>> on the connection (either ODBC or thru the libpq).
> >>
> >> Event triggers are fired by database events not by external prompting,
> >> so you do not need to have your code execute the trigger. You do have
> >> the option of disabling/enabling then though:
> >>
> >> https://www.postgresql.org/docs/10/sql-altereventtrigger.html
> > .
> > Yes, but the code to the event triogger needs to be written and then 
> > executed on
> > connection, right?
> >
> > So, this is what I'm thingking:
> >
> > [code]
> > ret = SQLExecute( m_hstmt, L"CREATE FUNCTION schema_watch_check()
> > RETURNS event_trigger AS schema_watch LANGUAGE C", SQL_NTS );
> > if( ret == SQL_SUCCESS || ret== SQL_SUCCESS_WITH_INFO )
> > {
> >  ret = SQLExecute( m_hstmt, L"CREATE EVENT TRIGGER schema_watch ON
> > ddl_command_end EXECUTE PROCEDURE schema_watch_check()", SQL_NTS );
> > }
> > [/code]
> >
> > and something to that extent on the libpq connection.
> >
> > Am I missing something here?
>
> Yes this:
>
> https://www.postgresql.org/docs/9.6/sql-createeventtrigger.html
>
> "CREATE EVENT TRIGGER creates a new event trigger. Whenever the
> designated event occurs and the WHEN condition associated with the
> trigger, if any, is satisfied, the trigger function will be executed.
> For a general introduction to event triggers, see Chapter 38. The user
> who creates an event trigger becomes its owner."
>
> So event triggers are associated with
> events(www.postgresql.org/docs/9.6/event-trigger-matrix.html) in a
> particular database. A rough description is that they are triggers on
> changes to the system catalogs.
> You could, I guess, create and drop them for each connection. To me it
> would seem more efficient to create them once. You then have the choice
> of leaving them running or using the ALTER command I posted previously
> to ENABLE/DISABLE them.

OK, so how do I do it?
There is no "CREATE EVENT TRIGGER IF NOT EXIST".

As I say - I'm trying to make it work from both ODBC and libpq
connection (one at a time).

>
> >
> > Now the other question is - how to make it work?
> > I can write the function code, compile it and place in the folder
> > where my executable is (or it should be in some postgreSQL dir?) and
> > then executing above code
> > will be enough. Is this correct?

Also - what about this?

And why did you say that saving the SQL commend is not a good idea.

What's better?

Thank you.

> >
> >>
> >>>
> >>> And then in my C++ code I will continuously query this temporary table.
> >>
> >> Why a temporary table? They are session specific and if the session
> >> aborts prematurely you will lose the information.
> >
> > Is there a better alternative?
> >
> > Thank you.
> >
> >>
> >>>
> >>> Or there is a better alternative on the algorithm?
> >>>
> >>> Thank you.
> >>>
> >>>>>>
> >>>>> Thank you.
> >>>>>
> >>>>
> >>>>
> >>>> --
> >>>> Adrian Klaver
> >>>> adrian.kla...@aklaver.com
> >>>
> >>
> >>
> >> --
> >> Adrian Klaver
> >> adrian.kla...@aklaver.com
> >
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com

Re: How to watch for schema changes

[Igor Korot]

Hi, Adrian,

On Mon, Dec 3, 2018 at 11:59 AM Adrian Klaver  wrote:
>
> On 12/3/18 9:53 AM, Igor Korot wrote:
> >> So event triggers are associated with
> >> events(www.postgresql.org/docs/9.6/event-trigger-matrix.html) in a
> >> particular database. A rough description is that they are triggers on
> >> changes to the system catalogs.
> >> You could, I guess, create and drop them for each connection. To me it
> >> would seem more efficient to create them once. You then have the choice
> >> of leaving them running or using the ALTER command I posted previously
> >> to ENABLE/DISABLE them.
> >
> > OK, so how do I do it?
> > There is no "CREATE EVENT TRIGGER IF NOT EXIST".
> >
> > As I say - I'm trying to make it work from both ODBC and libpq
> > connection (one at a time)
>
> Why? Just create the trigger once in a script. Event triggers are an
> attribute of the database and stay with it until they are dropped. If
> you want to turn then on and off use the ALTER  EVENT TRIGGER
> ENABLE/DISABLE. If you insist on recreating them on each connection then:
>
> https://www.postgresql.org/docs/10/sql-dropeventtrigger.html
> DROP EVENT TRIGGER [ IF EXISTS ] name [ CASCADE | RESTRICT ]
> CREATE EVENT TRIGGER ...

I was hoping to create a software which will be just "install-and-use".
No additional script running or database changes is required.

But I will probably create it on every connection and delete on the
disconnect (see above).

>
> >
> >>
> >>>
> >>> Now the other question is - how to make it work?
> >>> I can write the function code, compile it and place in the folder
> >>> where my executable is (or it should be in some postgreSQL dir?) and
> >>> then executing above code
> >>> will be enough. Is this correct?
> >
> > Also - what about this?
> >
> > And why did you say that saving the SQL commend is not a good idea.
> >
> > What's better?
>
> See above.
>
> >
> > Thank you.
> >
> >>>
> >>>>
> >>>>>
> >>>>> And then in my C++ code I will continuously query this temporary table.
> >>>>
> >>>> Why a temporary table? They are session specific and if the session
> >>>> aborts prematurely you will lose the information.
> >>>
> >>> Is there a better alternative?
> >>>
> >>> Thank you.
> >>>
> >>>>
> >>>>>
> >>>>> Or there is a better alternative on the algorithm?
> >>>>>
> >>>>> Thank you.
> >>>>>
> >>>>>>>>
> >>>>>>> Thank you.
> >>>>>>>
> >>>>>>
> >>>>>>
> >>>>>> --
> >>>>>> Adrian Klaver
> >>>>>> adrian.kla...@aklaver.com
> >>>>>
> >>>>
> >>>>
> >>>> --
> >>>> Adrian Klaver
> >>>> adrian.kla...@aklaver.com
> >>>
> >>
> >>
> >> --
> >> Adrian Klaver
> >> adrian.kla...@aklaver.com
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com

Re: How to watch for schema changes

[Igor Korot]

Hi, Adrian,

On Mon, Dec 3, 2018 at 4:10 PM Adrian Klaver  wrote:
>
> On 12/3/18 1:53 PM, Igor Korot wrote:
> > Hi, Adrian,
>
> >> Why? Just create the trigger once in a script. Event triggers are an
> >> attribute of the database and stay with it until they are dropped. If
> >> you want to turn then on and off use the ALTER  EVENT TRIGGER
> >> ENABLE/DISABLE. If you insist on recreating them on each connection then:
> >>
> >> https://www.postgresql.org/docs/10/sql-dropeventtrigger.html
> >> DROP EVENT TRIGGER [ IF EXISTS ] name [ CASCADE | RESTRICT ]
> >> CREATE EVENT TRIGGER ...
> >
> > I was hoping to create a software which will be just "install-and-use".
> > No additional script running or database changes is required.
>
> After I hit reply my subconscious kicked in and pointed out something:)
>
> If there are no database changes why do you need to track schema changes?

That was a bad word selection. ;-)

What I mean to say was "no schema changes/server changes that comes
independently
of the program install". Or something to that extent.

Sorry, ESL person here.

Thank you.

>
> >
> > But I will probably create it on every connection and delete on the
> > disconnect (see above).
> >
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com

Re: How to watch for schema changes

[Igor Korot]

Hi, Adrian,

On Mon, Dec 3, 2018 at 5:15 PM Adrian Klaver  wrote:
>
> On 12/3/18 3:00 PM, Igor Korot wrote:
> > Hi, Adrian,
> >
> > On Mon, Dec 3, 2018 at 4:10 PM Adrian Klaver  
> > wrote:
> >>
> >> On 12/3/18 1:53 PM, Igor Korot wrote:
> >>> Hi, Adrian,
> >>
> >>>> Why? Just create the trigger once in a script. Event triggers are an
> >>>> attribute of the database and stay with it until they are dropped. If
> >>>> you want to turn then on and off use the ALTER  EVENT TRIGGER
> >>>> ENABLE/DISABLE. If you insist on recreating them on each connection then:
> >>>>
> >>>> https://www.postgresql.org/docs/10/sql-dropeventtrigger.html
> >>>> DROP EVENT TRIGGER [ IF EXISTS ] name [ CASCADE | RESTRICT ]
> >>>> CREATE EVENT TRIGGER ...
> >>>
> >>> I was hoping to create a software which will be just "install-and-use".
> >>> No additional script running or database changes is required.
> >>
> >> After I hit reply my subconscious kicked in and pointed out something:)
> >>
> >> If there are no database changes why do you need to track schema changes?
> >
> > That was a bad word selection. ;-)
> >
> > What I mean to say was "no schema changes/server changes that comes
> > independently
> > of the program install". Or something to that extent.
>
> Which circles back around to the same question:
>
> If there are to be no schema changes after the install why track them on
> subsequent connections?
>
> Or maybe an explanation of what you are trying to achieve would make
> things clearer?

Ok, it probably will make things clearer.
So I install my program perform some queries and exit.
At the same time if the user will create or delete a table the program should
pick up those changes and act accordingly.

I was hoping to do the watching initialization dynamically, but it looks as it
is more pain and I can probably try to execute the script during the
installation.

So basically I will create the function in C, compile it and then
during the program installation
create a trigger with that function.
Then in my program I will execute "LISTEN" command and act accordingly.

Am I right?

And executing LISTEN will also work for ODBC connection, right?

Thank you.

>
>
>
> >
> > Sorry, ESL person here.
> >
> > Thank you.
> >
> >>
> >>>
> >>> But I will probably create it on every connection and delete on the
> >>> disconnect (see above).
> >>>
> >>
> >> --
> >> Adrian Klaver
> >> adrian.kla...@aklaver.com
> >
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com

Re: How to watch for schema changes

[Igor Korot]

Igor,

On Tue, Dec 4, 2018 at 8:20 AM Igor Neyman  wrote:
>
>
> -Original Message-
> From: Igor Korot [mailto:ikoro...@gmail.com]
> Sent: Monday, December 03, 2018 8:29 AM
> To: Adrian Klaver 
> Cc: pgsql-general 
> Subject: Re: How to watch for schema changes
>
> ...
>
> And executing LISTEN will also work for ODBC connection, right?
>
> Thank you.
>
> ___
>
> It's been years since we dealt with this problem, so the details are fuzzy.
>
> All applications in the package we develop connect to PG using ODBC, but one 
> app that's using LISTEN is connecting to PG through native interface 
> libpq.dll, ODBC didn't work for that purpose, at least at the time.

I will try it and report back.
Out of curiosity - when was the last time you tried?

Thank you.

>
> Regards,
> Igor Neyman

IF NOT EXIST

[Igor Korot]

Hi, ALL,
I have a following statement:

IF NOT EXIST( SELECT 1 SELECT 1 FROM pg_proc AS proc, pg_namespace AS
ns ) CREATE FUNCTION();

Unfortunately trying to execute it thru the ODBC interface with:

ret = SQLExecDirect( m_hstmt, query, SQL_NTS );

gives syntax error near IF.

What is the proper way to do that?

Thank you.

NOTIFY/LISTEN with ODBC interface

[Igor Korot]

Hi,
Is there a way to write a code found at
https://www.postgresql.org/docs/9.1/libpq-example.html
Example 2 with the ODBC interface?

Thank you.

Re: NOTIFY/LISTEN with ODBC interface

[Igor Korot]

Hi,

On Mon, Dec 24, 2018 at 5:25 AM Danny Severns  wrote:
>
> Is your question "can it be done" or "is someone willing to do it" or both?

Pretty much both.

Looking at the sample on the page it retrieves the connection socket.
And I know that there is no ODBC function that can retrieve this.

So can this be done with ODBC interface, and if yes - how?

Thank you.

>
> -----Original Message-
> From: Igor Korot 
> Sent: Sunday, December 23, 2018 9:37 PM
> To: PostgreSQL ODBC list ; pgsql-general 
> 
> Subject: NOTIFY/LISTEN with ODBC interface
>
> Hi,
> Is there a way to write a code found at
> https://www.postgresql.org/docs/9.1/libpq-example.html
> Example 2 with the ODBC interface?
>
> Thank you.
>

Re: NOTIFY/LISTEN with ODBC interface

[Igor Korot]

Hi, Clemens, et al,

On Tue, Dec 25, 2018 at 12:23 AM Clemens Ladisch  wrote:
>
> Igor Korot wrote:
> >Is there a way to write [LISTEN/NOTIFY] with the ODBC interface?
>
> At the moment, no.
>
> ODBC is a standard interface, so there are no functions for this defined.
>
> It might be possible to add PG-specific extensions to the ODBC driver,
> but this has not (yet) be done.

Thank you for the reply.
I guess I will just have to implement minimal schema watching algorithm.


>
>
> Regards,
> Clemens

Error on Windows

[Igor Korot]

Hi, ALL,
The following code compiles and executes but returns an error:

[quote]
Invalid byte sequence for encoding UTF8
[/quote]

[code]
char *values[2];
values[0] = NULL, values[1] = NULL;
values[0] = new char[schemaName.length() + 1];
values[1] = new char[tableName.length() + 1];
memset( values[0], '\0', schemaName.length() + 1 );
memset( values[1], '\0', tableName.length() + 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();
int len2 = (int) tableName.length();
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 );
}
[/code]

in the "err" variable.

Looking under MSVC debugger I see for a tableName a following sequence:

97 98 99 223

What can I do to eliminate the error?

Thank you.

libpq bug?

[Igor Korot]

Hi, ALL,
Following 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;
values[0] = new char[schemaName.length() + 1];
values[1] = new char[tableName.length() + 1];
memset( values[0], '\0', schemaName.length() + 1 );
memset( values[1], '\0', tableName.length() + 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();
int len2 = (int) tableName.length();
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;
}

when ran with the call of

GetTableOwner( "public", "abcß", owner, errorMsg );

returns:

ERROR: Invalid byte sequence for encoding UTF8.

Does this mean I found the bug in the library?

Any idea what I can do?

Thank you.

Re: libpq bug?

[Igor Korot]

Hi,

On Fri, Dec 28, 2018 at 4:51 PM patrick keshishian  wrote:
>
>
> On Fri, Dec 28, 2018 at 2:00 PM Igor Korot  wrote:
>>
>> Hi, ALL,
>> Following 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;
>> values[0] = new char[schemaName.length() + 1];
>> values[1] = new char[tableName.length() + 1];
>> memset( values[0], '\0', schemaName.length() + 1 );
>> memset( values[1], '\0', tableName.length() + 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();
>> int len2 = (int) tableName.length();
>> 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;
>> }
>>
>> when ran with the call of
>>
>> GetTableOwner( "public", "abcß", owner, errorMsg );
>>
>> returns:
>>
>> ERROR: Invalid byte sequence for encoding UTF8.
>>
>> Does this mean I found the bug in the library?
>
>
> The bug is in your C++ code. "abcß" as tableName.lenght() (wstring) returns 4 
> (as in four characters) not number of bytes required to represent the 
> intended string: 61 62 63 c3 9f
> Since the last character is a 2 bytes in length. Therefore, your call to 
> PQexecParams() specifies a shorter length and hence an invalid UTF-8 sequence.
>
> Furthermore, your value[] array allocation is in error since wstring::length 
> returns number of characters, not number of bytes. so you will end up with 
> buffer-overflows.

So I should use
https://stackoverflow.com/questions/9278723/how-can-i-get-the-byte-size-of-stdwstring
in both places?

Thank you.

>
> HTH,
> --patrick
>
>
>>
>> Any idea what I can do?
>>
>> Thank you.
>>