Re: Query help

[Peter J. Holzer]

On 2019-01-26 18:04:23 -0500, Chuck Martin wrote:
> I'm having trouble formulating a query. This is a simplified version of the
> tables:
> 
> ombcase
> 
> case_pkey integer, primary key
> casename varchar
> insdatetime timestamp w/o time zone
> status_fkey integer, foreign key
> 
> status
> 
> status_pkey integer, primary key
> statusid varchar
> 
> statuschange
> 
> statuschange_pkey integer, primary key
> insdatetime timestamp w/o time zone
> ombcase_fkey integer, foreign key
> oldstatus_fkey integer, foreign key
> newstatus_fkey integer, foreign key
> active integer, not nullable
> 
> The idea should be obvious, but to explain, insdatetime is set when a new
> record is created in any table. All records in ombcase have a foreign key to
> status that can't be null. When status changes, a record is created in
> statuschange recording the old and new status keys, and the time (etc). 
> 
> The goal is to find records in ombcase that have not had a status change in xx
> days. If the status has not changed, there will be no statuschange record. 

The easiest way is to use set operations:

select case_pkey from ombcase;
gives you all the ombcase ids.

select ombcase_fkey from statuschange where insdatetime >= now()::date - xx;
gives you all ombcase ids which had a status change in the last xx days.

Therefore,
select case_pkey from ombcase
except
select ombcase_fkey from statuschange where insdatetime >= now()::date - xx;
gives you all ombcase ids which did /not/ have a status change in the
last xx days.


Another way would be to use a CTE
(https://www.postgresql.org/docs/10/queries-with.html) to extract the
last status change for each ombcase and then do a left join of ombcase
to that CTE.

hp


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


signature.asc
Description: PGP signature

Implementing an expanded object in C

[Michel Pelletier]

Hello,

Apologies in advance for the long question.  I've made a lot of progress on
my GraphBLAS extension and getting close to having most of the API usefully
exposed to postgres, but I'm been struggling with an issue related to when
i switched to using an expanded representation of matrix types.

I've tried to follow closely how arrays work, but the answer still eludes
me.  The situation is slightly different in that where an array's flat
representation is useful, a sparse matrix's flat form is an edge-list, so
it's not useful unexpanded so I have a macro, PGGRB_GETARG_MATRIX(n) always
returns the expanded form by checking VARATT_IS_EXTERNAL_EXPANDED_RW and
then DatumGetEOHP if true, otherwise expanding from the flat representation:

https://github.com/michelp/pggraphblas/blob/bug/eohsegfault/src/matrix.c#L203

I also have a PGGRB_RETURN_MATRIX(m) macro that calls `return
EOHPGetRWDatum(&(A)->hdr)`

https://github.com/michelp/pggraphblas/blob/bug/eohsegfault/src/pggraphblas.h#L140

This chain of calls works for me in some cases, for example an operator
function, 'matrix_mxm' which overloads the '*' operator, can be used to
multiply two matrices:

postgres=# select '{{0,1,2},{1,2,0},{4,5,6}}'::matrix *
'{{0,1,2},{1,2,0},{4,5,6}}'::matrix;
   ?column?
--
 {{0,1,2},{2,0,1},{20,30,24}}
(1 row)

Works great! Internally this was `matrix_out(matrix_mxm(matrix_in(),
matrix_in()))` where the data flow fine both in and out of the functions.
But I have another function, 'matrix_agg', that aggregates edges from a
query into a matrix.  It builds and returns the result matrix similarly to
matrix_mxm does and returns it using the same macro, but matrix_out's call
to get the agregates final value segfaults.

select matrix_agg(i, j, v) from edges;  -- segfaults in matrix_out at
PG_GETARG_MATRIX(0)

at

https://github.com/michelp/pggraphblas/blob/bug/eohsegfault/src/matrix.c#L207

Afaict matrix_agg and matrix_mxm are both creating and returning matrices
the same way, using the same function to build them and the same macro that
`return EOHPGetRWDatum(&(A)->hdr)`, but when matrix_out fetches the
argument to print the result it bombs on the aggregate's final value.  The
only salient different I can see if the agg's final function calls:

  if (!AggCheckCallContext(fcinfo, &resultcxt)) {
resultcxt = CurrentMemoryContext;
  }

  oldcxt = MemoryContextSwitchTo(resultcxt);
  // do matrix creation stuff
  MemoryContextSwitchTo(oldcxt);


But even if I remove that and do not switch contexts, it still crashes the
same way.

It must be possible to return expanded objects from aggregates so I'm
clearly doing something wrong.  The final function actually worked before I
was using expanded representation and just using PG_RETURN_POINTER, but
despite having all these clues I've been staring at this segfault in gdb
for a couple of days now.

Any pointers on this subject would be greatly appreciated!  I know someone
else out there recently was working on an expanded object posted on the
list, if you don't see this, I may reach out to you. :)

-Michel

Does creating readOnly connections, when possible, free up resources in Postgres?

[David Kremer]

I have an API server and I'm trying to be conscientious managing Postgres's resources carefully. On the client side, I have a Hikari Pool.


Usually when I need a connection, I simply create a default read/write connection, even if I don't plan to make any updates or inserts or hold any locks. But most of my database connections are in fact read-only.

 

I saw that when you create a JDBC connection, you can specify readOnly=true. Would doing so somehow help Postgres manage its other connections? Perhaps Postgres, knowing that a connection is readOnly and will never even attempt to do an update, will free up some internal resources for other connections. Is this accurate?

 

Does creating readOnly connections, when possible, free up resources in Postgres?

[David Kremer]

(resending to remove HTML formatting)

I have an API server and I'm trying to be conscientious managing Postgres's 
resources carefully. On the client side, I have a Hikari Pool.

Usually when I need a connection, I simply create a default read/write 
connection, even if I don't plan to make any updates or inserts or hold any 
locks. But most of my database connections are in fact read-only.

I saw that when you create a JDBC connection, you can specify readOnly=true. 
Would doing so somehow help Postgres manage its other connections? Perhaps 
Postgres, knowing that a connection is readOnly and will never even attempt to 
do an update, will free up some internal resources for other connections. Is 
this accurate?

Re: Does creating readOnly connections, when possible, free up resources in Postgres?

[Tom Lane]

"David Kremer"  writes:
> I saw that when you create a JDBC connection, you can specify readOnly=true. 
> Would doing so somehow help Postgres manage its other connections? Perhaps 
> Postgres, knowing that a connection is readOnly and will never even attempt 
> to do an update, will free up some internal resources for other connections. 
> Is this accurate?

No.

regards, tom lane

Re: Query help

[Adrian Klaver]

On 1/26/19 3:04 PM, Chuck Martin wrote:
I'm having trouble formulating a query. This is a simplified version of 
the tables:


ombcase

case_pkey integer, primary key
casename varchar
insdatetime timestamp w/o time zone
status_fkey integer, foreign key

status

status_pkey integer, primary key
statusid varchar

statuschange

statuschange_pkey integer, primary key
insdatetime timestamp w/o time zone
ombcase_fkey integer, foreign key
oldstatus_fkey integer, foreign key
newstatus_fkey integer, foreign key
active integer, not nullable

The idea should be obvious, but to explain, insdatetime is set when a 
new record is created in any table. All records in ombcase have a 
foreign key to status that can't be null. When status changes, a record 
is created in statuschange recording the old and new status keys, and 
the time (etc).


The goal is to find records in ombcase that have not had a status change 
in xx days. If the status has not changed, there will be no statuschange 
record.


This query returns the age of each ombcase and the last statuschange 
record, but only if there is a statuschange record:


--Finds the age and last status change for open cases, but not age of 
cases with no status change


SELECT casename, age(ombcase.insdatetime) AS caseage, 
age(laststatuschange.created_at) AS statusage


FROM

(SELECT

case_fkey, MAX(insdatetime) AS created_at

FROM

statuschange

GROUP BY

case_fkey) AS laststatuschange

INNER JOIN

ombcase

ON

laststatuschange.case_fkey = case_pkey

RIGHT JOIN status

ON status_fkey = status_pkey

WHERE lower(statusid) NOT LIKE ('closed%')

AND case_pkey <> 0


I want to use coalesce 
(age(ombcase.insdatetime),age(statuschange.insdatetime) ) to return the 
time that a case has been in a status—or without a status change. But 
first I have to find the cases with no statuschange record. I was able 
to do that, too, using this query:


Outline form:

1) If a record is in ombcase it has a status('in a status') by definition.

From query below you are not looking for just records in ombcase, but 
those that have a statusid other then 'closed%' in status table.


2) For the criteria in 1) you want to find the age of the last statuschange.

To me that leads to something like:

SELECT
case_pkey
FROM
ombcase AS
JOIN
status
ON
ombcase.case_pkey = status.status_fkey
LEFT JOIN
statuschange
ON  -- Or statuschange.ombcase_fkey. Not clear from above.
statuschange.case_fkey = ombcase.status_pkey
GROUP BY
ombcase.pkey
HAVING
status.LOWER(statusid) NOT LIKE ('closed%')
AND
max(coalesce(statuschange.insdatetime, ombcase.insdatetime))
< 'some date'

Obviously not tested.




--find cases in status too long

SELECT  casename, coalesce 
(age(ombcase.insdatetime),age(statuschange.insdatetime) )


FROM ombcase

LEFT JOIN statuschange

ON case_fkey = case_pkey

LEFT JOIN status

ON status_fkey = status_pkey

AND lower(statusid) NOT LIKE ('closed%')

AND coalesce ( age(ombcase.insdatetime), age(statuschange.insdatetime) ) 
 > '2 months'



But this query will return all statuschange records for an ombcase 
record that has multiple ones.



Any suggestions on how to combine the two ideas?


Chuck Martin
Avondale Software
--
Chuck Martin
Avondale Software



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

Error message restarting a database

[Begin Daniel]

I worked with a PostgreSQL database (9.3) for a couple of years on my PC. I 
shut down the PostgreSQL server about a year ago and I obviously did not access 
the database since. Now I am trying to access again to the database but I get 
the following error message...

FATAL: *"pg_tblspc/113608/PG. 9.3_ 201306121/18364" is not a valid data 
directory
DETAIL: File *" pg_tblspc/113608/PG. 9.3_ 201306121/18364/PG_VERSION" is 
missing.

Anyone has an idea on how to solve this problem?

Daniel

Re: Error message restarting a database

[Adrian Klaver]

On 1/27/19 12:23 PM, Begin Daniel wrote:
I worked with a PostgreSQL database (9.3) for a couple of years on my 
PC. I shut down the PostgreSQL server about a year ago and I obviously 
did not access the database since. Now I am trying to access again to 
the database but I get the following error message…


FATAL: ·"pg_tblspc/113608/PG. 9.3_ 201306121/18364" is not a valid data 
directory


DETAIL: File ·" pg_tblspc/113608/PG. 9.3_ 201306121/18364/PG_VERSION" is 
missing.


Anyone has an idea on how to solve this problem?


What OS and version?

Best guess is your OS updated Postgres in the past year and the newer 
version is trying to load an older data directory.




Daniel




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

Re: Error message restarting a database

[Adrian Klaver]

On 1/27/19 12:56 PM, Begin Daniel wrote:

Not a good day, forgot to actually Cc list last time.


-Original Message-
From: Adrian Klaver [mailto:adrian.kla...@aklaver.com]
Sent: Sunday, January 27, 2019 15:43
To: Begin Daniel
Subject: Re: Error message restarting a database

On 1/27/19 12:38 PM, Begin Daniel wrote:
Please reply to list also
Ccing list.

-Original Message-
From: Adrian Klaver [mailto:adrian.kla...@aklaver.com]
Sent: Sunday, January 27, 2019 15:33
To: Begin Daniel; pgsql-general@lists.postgresql.org
Subject: Re: Error message restarting a database

On 1/27/19 12:23 PM, Begin Daniel wrote:

I worked with a PostgreSQL database (9.3) for a couple of years on my
PC. I shut down the PostgreSQL server about a year ago and I obviously
did not access the database since. Now I am trying to access again to
the database but I get the following error message...

FATAL: *"pg_tblspc/113608/PG. 9.3_ 201306121/18364" is not a valid data
directory

DETAIL: File *" pg_tblspc/113608/PG. 9.3_ 201306121/18364/PG_VERSION" is
missing.

Anyone has an idea on how to solve this problem?


What OS and version?
Best guess is your OS updated Postgres in the past year and the newer
version is trying to load an older data directory.



Daniel



OS and version...
Windows 10 Pro, Version 1809 installed on 2019-01-11
OS Build 17763.253


Whoops, just realized the errors are pointing at a tablespace/dir.

Does the directory in the error message exist?





I have multiple tablespaces. According to the definition of my tablespaces, the 
error point on the following directory:  K:\pgsqlData\PG_9.3_201306121\18364
The directory exists with 888 items in it, but there is no file named PG_VERSION



Are all the tablespaces connected to a single Postgres instance?

Are you still using Postgres 9.3?

Does the  Postgres top level data directory still exist?





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

RE: Error message restarting a database

[Begin Daniel]

-Original Message-
From: Adrian Klaver [mailto:adrian.kla...@aklaver.com] 
Sent: Sunday, January 27, 2019 16:15
To: Begin Daniel
Cc: pgsql-general
Subject: Re: Error message restarting a database

On 1/27/19 12:56 PM, Begin Daniel wrote:

Not a good day, forgot to actually Cc list last time.

> -Original Message-
> From: Adrian Klaver [mailto:adrian.kla...@aklaver.com]
> Sent: Sunday, January 27, 2019 15:43
> To: Begin Daniel
> Subject: Re: Error message restarting a database
> 
> On 1/27/19 12:38 PM, Begin Daniel wrote:
> Please reply to list also
> Ccing list.
>> -Original Message-
>> From: Adrian Klaver [mailto:adrian.kla...@aklaver.com]
>> Sent: Sunday, January 27, 2019 15:33
>> To: Begin Daniel; pgsql-general@lists.postgresql.org
>> Subject: Re: Error message restarting a database
>>
>> On 1/27/19 12:23 PM, Begin Daniel wrote:
>>> I worked with a PostgreSQL database (9.3) for a couple of years on my
>>> PC. I shut down the PostgreSQL server about a year ago and I obviously
>>> did not access the database since. Now I am trying to access again to
>>> the database but I get the following error message...
>>>
>>> FATAL: *"pg_tblspc/113608/PG. 9.3_ 201306121/18364" is not a valid data
>>> directory
>>>
>>> DETAIL: File *" pg_tblspc/113608/PG. 9.3_ 201306121/18364/PG_VERSION" is
>>> missing.
>>>
>>> Anyone has an idea on how to solve this problem?
>>
>> What OS and version?
>> Best guess is your OS updated Postgres in the past year and the newer
>> version is trying to load an older data directory.
>>
>>>
>>> Daniel
>>>
>>
>> OS and version...
>> Windows 10 Pro, Version 1809 installed on 2019-01-11
>> OS Build 17763.253
> 
> Whoops, just realized the errors are pointing at a tablespace/dir.
> 
> Does the directory in the error message exist?
> 
>>
>>
> I have multiple tablespaces. According to the definition of my tablespaces, 
> the error point on the following directory:  
> K:\pgsqlData\PG_9.3_201306121\18364
> The directory exists with 888 items in it, but there is no file named 
> PG_VERSION
> 

Are all the tablespaces connected to a single Postgres instance?

Are you still using Postgres 9.3?

Does the  Postgres top level data directory still exist?


Right, not a good day ;-)
- All tablespaces are connected to a single Postgres database instance
- I am still using Postgres 9.3 since I did not change anything related to 
PostgreSQL since I shut down the server a year ago
- I am not sure what you mean by top level directory. 

I am using PgAdmin to access my PostgreSQL databases. I can access the default 
database (postgres), tablespace definitions, group roles and login roles. The 
other database instance I have is the one that send me error messages.

Re: Query help

[Chuck Martin]

Chuck Martin
Avondale Software


On Sun, Jan 27, 2019 at 2:55 PM Adrian Klaver 
wrote:

> On 1/26/19 3:04 PM, Chuck Martin wrote:
> [snip]
> Outline form:
>
> 1) If a record is in ombcase it has a status('in a status') by definition.
>
>  From query below you are not looking for just records in ombcase, but
> those that have a statusid other then 'closed%' in status table.
>
> 2) For the criteria in 1) you want to find the age of the last
> statuschange.
>
> To me that leads to something like:
>
> SELECT
> case_pkey
> FROM
> ombcase AS
> JOIN
> status
> ON
> ombcase.case_pkey = status.status_fkey
> LEFT JOIN
> statuschange
> ON  -- Or statuschange.ombcase_fkey. Not clear from above.
> statuschange.case_fkey = ombcase.status_pkey
> GROUP BY
> ombcase.pkey
> HAVING
> status.LOWER(statusid) NOT LIKE ('closed%')
> AND
> max(coalesce(statuschange.insdatetime, ombcase.insdatetime))
> < 'some date'
>
> Obviously not tested.
>

Thanks, Adrian. This got me a lot closer, but I'm puzzled by the number of
records returned. There are 3120 ombcase records with a statusid that is <>
'closed%':

SELECT count(ombcase.case_pkey)

FROM ombcase,status

WHERE ombcase.status_fkey = status.status_pkey  AND lower(status.statusid)
NOT LIKE  ('closed%')


But 3378 are returned by:

SELECT  ombcase.case_pkey, ombcase.casename,
COALESCE(AGE(statuschange.insdatetime), AGE(ombcase.insdatetime)) AS
age_in_status

FROM ombcase

INNER JOIN status

ON ombcase.status_fkey = status.status_pkey

LEFT JOIN statuschange

ON statuschange.case_fkey = ombcase.case_pkey

GROUP BY ombcase.case_pkey, status.statusid, statuschange.insdatetime,
ombcase.insdatetime

HAVING LOWER(status.statusid) NOT LIKE ('closed%')

AND ombcase.case_pkey <> 0

AND MAX(COALESCE(AGE(statuschange.insdatetime), AGE(ombcase.insdatetime)))
> '2 months'
ORDER BY age_in_status DESC

I don't know where the extra 258 records came from, and I think I need to
keep working on it until the query returns 3120 records.

Re: Error message restarting a database

[Adrian Klaver]

On 1/27/19 1:38 PM, Begin Daniel wrote:

-Original Message-
From: Adrian Klaver [mailto:adrian.kla...@aklaver.com]
Sent: Sunday, January 27, 2019 16:15
To: Begin Daniel
Cc: pgsql-general
Subject: Re: Error message restarting a database

On 1/27/19 12:56 PM, Begin Daniel wrote:

Not a good day, forgot to actually Cc list last time.


-Original Message-
From: Adrian Klaver [mailto:adrian.kla...@aklaver.com]
Sent: Sunday, January 27, 2019 15:43
To: Begin Daniel
Subject: Re: Error message restarting a database

On 1/27/19 12:38 PM, Begin Daniel wrote:
Please reply to list also
Ccing list.

-Original Message-
From: Adrian Klaver [mailto:adrian.kla...@aklaver.com]
Sent: Sunday, January 27, 2019 15:33
To: Begin Daniel; pgsql-general@lists.postgresql.org
Subject: Re: Error message restarting a database

On 1/27/19 12:23 PM, Begin Daniel wrote:

I worked with a PostgreSQL database (9.3) for a couple of years on my
PC. I shut down the PostgreSQL server about a year ago and I obviously
did not access the database since. Now I am trying to access again to
the database but I get the following error message...

FATAL: *"pg_tblspc/113608/PG. 9.3_ 201306121/18364" is not a valid data
directory

DETAIL: File *" pg_tblspc/113608/PG. 9.3_ 201306121/18364/PG_VERSION" is
missing.

Anyone has an idea on how to solve this problem?


What OS and version?
Best guess is your OS updated Postgres in the past year and the newer
version is trying to load an older data directory.



Daniel



OS and version...
Windows 10 Pro, Version 1809 installed on 2019-01-11
OS Build 17763.253


Whoops, just realized the errors are pointing at a tablespace/dir.

Does the directory in the error message exist?





I have multiple tablespaces. According to the definition of my tablespaces, the 
error point on the following directory:  K:\pgsqlData\PG_9.3_201306121\18364
The directory exists with 888 items in it, but there is no file named PG_VERSION



Are all the tablespaces connected to a single Postgres instance?

Are you still using Postgres 9.3?

Does the  Postgres top level data directory still exist?


Right, not a good day ;-)
- All tablespaces are connected to a single Postgres database instance
- I am still using Postgres 9.3 since I did not change anything related to 
PostgreSQL since I shut down the server a year ago
- I am not sure what you mean by top level directory.


PGDATA from here:
https://www.postgresql.org/docs/10/storage-file-layout.html

I am going to say PGDATA exists given the below.




I am using PgAdmin to access my PostgreSQL databases. I can access the default 
database (postgres), tablespace definitions, group roles and login roles. The 
other database instance I have is the one that send me error messages.



To be clear you are running only one instance(cluster) of Postgres and 
that instance has several databases defined in it, correct?


If you go to PGDATA.pg_tblspc  do you have links to the tablespaces?




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

Re: Query help

[Adrian Klaver]

On 1/27/19 1:50 PM, Chuck Martin wrote:


Chuck Martin
Avondale Software


On Sun, Jan 27, 2019 at 2:55 PM Adrian Klaver > wrote:


On 1/26/19 3:04 PM, Chuck Martin wrote:
[snip]
Outline form:

1) If a record is in ombcase it has a status('in a status') by
definition.

  From query below you are not looking for just records in ombcase, but
those that have a statusid other then 'closed%' in status table.

2) For the criteria in 1) you want to find the age of the last
statuschange.

To me that leads to something like:

SELECT
         case_pkey
FROM
         ombcase AS
JOIN
         status
ON
         ombcase.case_pkey = status.status_fkey
LEFT JOIN
         statuschange
ON  -- Or statuschange.ombcase_fkey. Not clear from above.
         statuschange.case_fkey = ombcase.status_pkey
GROUP BY
         ombcase.pkey
HAVING
         status.LOWER(statusid) NOT LIKE ('closed%')
AND
         max(coalesce(statuschange.insdatetime, ombcase.insdatetime))
         < 'some date'

Obviously not tested.


Thanks, Adrian. This got me a lot closer, but I'm puzzled by the number 
of records returned. There are 3120 ombcase records with a statusid that 
is <> 'closed%'




SELECT count(ombcase.case_pkey)

FROM ombcase,status

WHERE ombcase.status_fkey = status.status_pkey  AND 
lower(status.statusid) NOT LIKE  ('closed%')


To get an apples to apples comparison what does below return?:

SELECT  count(ombcase.case_pkey)
FROM ombcase

INNER JOIN status

ON ombcase.status_fkey = status.status_pkey

LEFT JOIN statuschange

ON statuschange.case_fkey = ombcase.case_pkey
AND
LOWER(status.statusid) NOT LIKE ('closed%')


Best guess is the 258 records are the ombcase records that have no 
statuschange records, brought in by the LEFT JOIN.





But 3378 are returned by:

SELECT  ombcase.case_pkey, ombcase.casename, 
COALESCE(AGE(statuschange.insdatetime), AGE(ombcase.insdatetime)) AS 
age_in_status


FROM ombcase

INNER JOIN status

ON ombcase.status_fkey = status.status_pkey

LEFT JOIN statuschange

ON statuschange.case_fkey = ombcase.case_pkey

GROUP BY ombcase.case_pkey, status.statusid, statuschange.insdatetime, 
ombcase.insdatetime


HAVING LOWER(status.statusid) NOT LIKE ('closed%')

AND ombcase.case_pkey <> 0

AND MAX(COALESCE(AGE(statuschange.insdatetime), 
AGE(ombcase.insdatetime))) > '2 months'


ORDER BY age_in_status DESC

I don't know where the extra 258 records came from, and I think I need 
to keep working on it until the query returns 3120 records.



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

RE: Error message restarting a database

[Begin Daniel]

-Original Message-
From: Adrian Klaver [mailto:adrian.kla...@aklaver.com] 
Sent: Sunday, January 27, 2019 16:52
To: Begin Daniel
Cc: pgsql-general
Subject: Re: Error message restarting a database

On 1/27/19 1:38 PM, Begin Daniel wrote:
> -Original Message-
> From: Adrian Klaver [mailto:adrian.kla...@aklaver.com]
> Sent: Sunday, January 27, 2019 16:15
> To: Begin Daniel
> Cc: pgsql-general
> Subject: Re: Error message restarting a database
> 
> On 1/27/19 12:56 PM, Begin Daniel wrote:
> 
> Not a good day, forgot to actually Cc list last time.
> 
>> -Original Message-
>> From: Adrian Klaver [mailto:adrian.kla...@aklaver.com]
>> Sent: Sunday, January 27, 2019 15:43
>> To: Begin Daniel
>> Subject: Re: Error message restarting a database
>>
>> On 1/27/19 12:38 PM, Begin Daniel wrote:
>> Please reply to list also
>> Ccing list.
>>> -Original Message-
>>> From: Adrian Klaver [mailto:adrian.kla...@aklaver.com]
>>> Sent: Sunday, January 27, 2019 15:33
>>> To: Begin Daniel; pgsql-general@lists.postgresql.org
>>> Subject: Re: Error message restarting a database
>>>
>>> On 1/27/19 12:23 PM, Begin Daniel wrote:
 I worked with a PostgreSQL database (9.3) for a couple of years on my
 PC. I shut down the PostgreSQL server about a year ago and I obviously
 did not access the database since. Now I am trying to access again to
 the database but I get the following error message...

 FATAL: *"pg_tblspc/113608/PG. 9.3_ 201306121/18364" is not a valid data
 directory

 DETAIL: File *" pg_tblspc/113608/PG. 9.3_ 201306121/18364/PG_VERSION" is
 missing.

 Anyone has an idea on how to solve this problem?
>>>
>>> What OS and version?
>>> Best guess is your OS updated Postgres in the past year and the newer
>>> version is trying to load an older data directory.
>>>

 Daniel

>>>
>>> OS and version...
>>> Windows 10 Pro, Version 1809 installed on 2019-01-11
>>> OS Build 17763.253
>>
>> Whoops, just realized the errors are pointing at a tablespace/dir.
>>
>> Does the directory in the error message exist?
>>
>>>
>>>
>> I have multiple tablespaces. According to the definition of my tablespaces, 
>> the error point on the following directory:  
>> K:\pgsqlData\PG_9.3_201306121\18364
>> The directory exists with 888 items in it, but there is no file named 
>> PG_VERSION
>>
> 
> Are all the tablespaces connected to a single Postgres instance?
> 
> Are you still using Postgres 9.3?
> 
> Does the  Postgres top level data directory still exist?
> 
> 
> Right, not a good day ;-)
> - All tablespaces are connected to a single Postgres database instance
> - I am still using Postgres 9.3 since I did not change anything related to 
> PostgreSQL since I shut down the server a year ago
> - I am not sure what you mean by top level directory.

PGDATA from here:
https://www.postgresql.org/docs/10/storage-file-layout.html

I am going to say PGDATA exists given the below.


> 
> I am using PgAdmin to access my PostgreSQL databases. I can access the 
> default database (postgres), tablespace definitions, group roles and login 
> roles. The other database instance I have is the one that send me error 
> messages.
> 

To be clear you are running only one instance(cluster) of Postgres and 
that instance has several databases defined in it, correct?

If you go to PGDATA.pg_tblspc  do you have links to the tablespaces?

I only installed one instance of Postgres on my PC, which I use to manage two 
databases (postgres and osmdump). osmdump is the database that complains.

Regarding the links to the tablespaces, I first ran the following request.
SELECT spcname FROM pg_tablespace; and got the following list.
"pg_default"
"pg_global"
"workspace"
"datadrive1"
"datadrive2"
"datadrive3"
"datadrive0"

I went to E:\pgsqlData\pg_tblspc and found the links to the five last 
tablespaces above (I manually created them, the first two were created when I 
installed Postgres). 
E:\pgsqlData\pg_tblspc \113608\PG_9.3_201306121\18364 link to the 888 items 
mentioned previously (physically stored in K:\pgsqlData\pg_tblspc...)
I also found a PG_VERSION file in E:\pgsqlData. The file contains the value 9.3

Daniel

Re: Implementing an expanded object in C

[Michel Pelletier]

Replying to my own problem here, I guess this was a situation where
explaining it in detail revealed the problem to me.  By specifying my type
is 'passedbyvalue' and 'alignment = double' it now works!

CREATE TYPE matrix (
internallength = 8,
input = matrix_in,
output = matrix_out,
passedbyvalue,
alignment = double
);

Thanks for being a sounding board.

-Michel

On Sun, Jan 27, 2019 at 8:59 AM Michel Pelletier 
wrote:

> Hello,
>
> Apologies in advance for the long question.  I've made a lot of progress
> on my GraphBLAS extension and getting close to having most of the API
> usefully exposed to postgres, but I'm been struggling with an issue related
> to when i switched to using an expanded representation of matrix types.
>
> I've tried to follow closely how arrays work, but the answer still eludes
> me.  The situation is slightly different in that where an array's flat
> representation is useful, a sparse matrix's flat form is an edge-list, so
> it's not useful unexpanded so I have a macro, PGGRB_GETARG_MATRIX(n) always
> returns the expanded form by checking VARATT_IS_EXTERNAL_EXPANDED_RW and
> then DatumGetEOHP if true, otherwise expanding from the flat representation:
>
>
> https://github.com/michelp/pggraphblas/blob/bug/eohsegfault/src/matrix.c#L203
>
> I also have a PGGRB_RETURN_MATRIX(m) macro that calls `return
> EOHPGetRWDatum(&(A)->hdr)`
>
>
> https://github.com/michelp/pggraphblas/blob/bug/eohsegfault/src/pggraphblas.h#L140
>
> This chain of calls works for me in some cases, for example an operator
> function, 'matrix_mxm' which overloads the '*' operator, can be used to
> multiply two matrices:
>
> postgres=# select '{{0,1,2},{1,2,0},{4,5,6}}'::matrix *
> '{{0,1,2},{1,2,0},{4,5,6}}'::matrix;
>?column?
> --
>  {{0,1,2},{2,0,1},{20,30,24}}
> (1 row)
>
> Works great! Internally this was `matrix_out(matrix_mxm(matrix_in(),
> matrix_in()))` where the data flow fine both in and out of the functions.
> But I have another function, 'matrix_agg', that aggregates edges from a
> query into a matrix.  It builds and returns the result matrix similarly to
> matrix_mxm does and returns it using the same macro, but matrix_out's call
> to get the agregates final value segfaults.
>
> select matrix_agg(i, j, v) from edges;  -- segfaults in matrix_out at
> PG_GETARG_MATRIX(0)
>
> at
>
>
> https://github.com/michelp/pggraphblas/blob/bug/eohsegfault/src/matrix.c#L207
>
> Afaict matrix_agg and matrix_mxm are both creating and returning matrices
> the same way, using the same function to build them and the same macro that
> `return EOHPGetRWDatum(&(A)->hdr)`, but when matrix_out fetches the
> argument to print the result it bombs on the aggregate's final value.  The
> only salient different I can see if the agg's final function calls:
>
>   if (!AggCheckCallContext(fcinfo, &resultcxt)) {
> resultcxt = CurrentMemoryContext;
>   }
>
>   oldcxt = MemoryContextSwitchTo(resultcxt);
>   // do matrix creation stuff
>   MemoryContextSwitchTo(oldcxt);
>
>
> But even if I remove that and do not switch contexts, it still crashes the
> same way.
>
> It must be possible to return expanded objects from aggregates so I'm
> clearly doing something wrong.  The final function actually worked before I
> was using expanded representation and just using PG_RETURN_POINTER, but
> despite having all these clues I've been staring at this segfault in gdb
> for a couple of days now.
>
> Any pointers on this subject would be greatly appreciated!  I know someone
> else out there recently was working on an expanded object posted on the
> list, if you don't see this, I may reach out to you. :)
>
> -Michel
>
>
>

Re: Query help

[Chuck Martin]

On Sun, Jan 27, 2019 at 8:07 AM Peter J. Holzer  wrote:

> On 2019-01-26 18:04:23 -0500, Chuck Martin wrote:
> [snip]
> > The idea should be obvious, but to explain, insdatetime is set when a new
> > record is created in any table. All records in ombcase have a foreign
> key to
> > status that can't be null. When status changes, a record is created in
> > statuschange recording the old and new status keys, and the time (etc).
> >
> > The goal is to find records in ombcase that have not had a status change
> in xx
> > days. If the status has not changed, there will be no statuschange
> record.
>
> The easiest way is to use set operations:
>
> select case_pkey from ombcase;
> gives you all the ombcase ids.
>
> select ombcase_fkey from statuschange where insdatetime >= now()::date -
> xx;
> gives you all ombcase ids which had a status change in the last xx days.
>
> Therefore,
> select case_pkey from ombcase
> except
> select ombcase_fkey from statuschange where insdatetime >= now()::date -
> xx;
> gives you all ombcase ids which did /not/ have a status change in the
> last xx days.
>

I was not familiar with set operations, but studied up a bit and thought I
was getting there. Not quite, though. I have two queries that individually
return 1) all ombcase records with no statuschange record, and 2) the
newest statuschange record for each case that has a statuschange record.
But just putting UNION between then doesn't work. Here are my queries:

--First, find all open cases with no statuschange record
SELECT

case_pkey,statuschange_pkey,case_fkey,ombcase.insdatetime,statuschange.insdatetime
FROM
ombcase
LEFT JOIN
statuschange
ON
  statuschange.case_fkey = case_pkey
  AND case_pkey <> 0
LEFT JOIN
status
 ON status_fkey = status_pkey
  WHERE lower(statusid) NOT LIKE  ('closed%')
  AND statuschange.statuschange_pkey IS NULL
UNION
  --Now find the last status change record for each case that has one
  SELECT DISTINCT ON (case_fkey)

case_pkey,statuschange_pkey,case_fkey,ombcase.insdatetime,statuschange.insdatetime
FROM
statuschange,ombcase,status
WHERE case_fkey = case_pkey
AND status_fkey = status_pkey
AND LOWER(statusid) NOT LIKE ('closed%')
ORDER BY case_fkey, statuschange.insdatetime DESC

If I run each part separately, I get the expected number of records.
When I combine them with UNION, I get "missing FROM-clause entry for
table "statuschange"
So I'm very close here, and these two return the exact number of
records I'm expecting. So I just need to get them added together. Then
I expect I can put the whole thing in a WHERE clause with "AND
ombcase.case_pkey IN ([the combined results])"


>
> Another way would be to use a CTE
> (https://www.postgresql.org/docs/10/queries-with.html) to extract the
> last status change for each ombcase and then do a left join of ombcase
> to that CTE.
>
> hp
>
>
> --
>_  | Peter J. Holzer| we build much bigger, better disasters now
> |_|_) || because we have much more sophisticated
> | |   | h...@hjp.at | management tools.
> __/   | http://www.hjp.at/ | -- Ross Anderson 
>

RE: Error message restarting a database

[Begin Daniel]

-Original Message-
From: Adrian Klaver [mailto:adrian.kla...@aklaver.com] 
Sent: Sunday, January 27, 2019 16:52
To: Begin Daniel
Cc: pgsql-general
Subject: Re: Error message restarting a database

On 1/27/19 1:38 PM, Begin Daniel wrote:
> -Original Message-
> From: Adrian Klaver [mailto:adrian.kla...@aklaver.com]
> Sent: Sunday, January 27, 2019 16:15
> To: Begin Daniel
> Cc: pgsql-general
> Subject: Re: Error message restarting a database
> 
> On 1/27/19 12:56 PM, Begin Daniel wrote:
> 
> Not a good day, forgot to actually Cc list last time.
> 
>> -Original Message-
>> From: Adrian Klaver [mailto:adrian.kla...@aklaver.com]
>> Sent: Sunday, January 27, 2019 15:43
>> To: Begin Daniel
>> Subject: Re: Error message restarting a database
>>
>> On 1/27/19 12:38 PM, Begin Daniel wrote:
>> Please reply to list also
>> Ccing list.
>>> -Original Message-
>>> From: Adrian Klaver [mailto:adrian.kla...@aklaver.com]
>>> Sent: Sunday, January 27, 2019 15:33
>>> To: Begin Daniel; pgsql-general@lists.postgresql.org
>>> Subject: Re: Error message restarting a database
>>>
>>> On 1/27/19 12:23 PM, Begin Daniel wrote:
 I worked with a PostgreSQL database (9.3) for a couple of years on my
 PC. I shut down the PostgreSQL server about a year ago and I obviously
 did not access the database since. Now I am trying to access again to
 the database but I get the following error message...

 FATAL: *"pg_tblspc/113608/PG. 9.3_ 201306121/18364" is not a valid data
 directory

 DETAIL: File *" pg_tblspc/113608/PG. 9.3_ 201306121/18364/PG_VERSION" is
 missing.

 Anyone has an idea on how to solve this problem?
>>>
>>> What OS and version?
>>> Best guess is your OS updated Postgres in the past year and the newer
>>> version is trying to load an older data directory.
>>>

 Daniel

>>>
>>> OS and version...
>>> Windows 10 Pro, Version 1809 installed on 2019-01-11
>>> OS Build 17763.253
>>
>> Whoops, just realized the errors are pointing at a tablespace/dir.
>>
>> Does the directory in the error message exist?
>>
>>>
>>>
>> I have multiple tablespaces. According to the definition of my tablespaces, 
>> the error point on the following directory:  
>> K:\pgsqlData\PG_9.3_201306121\18364
>> The directory exists with 888 items in it, but there is no file named 
>> PG_VERSION
>>
> 
> Are all the tablespaces connected to a single Postgres instance?
> 
> Are you still using Postgres 9.3?
> 
> Does the  Postgres top level data directory still exist?
> 
> 
> Right, not a good day ;-)
> - All tablespaces are connected to a single Postgres database instance
> - I am still using Postgres 9.3 since I did not change anything related to 
> PostgreSQL since I shut down the server a year ago
> - I am not sure what you mean by top level directory.

PGDATA from here:
https://www.postgresql.org/docs/10/storage-file-layout.html

I am going to say PGDATA exists given the below.


> 
> I am using PgAdmin to access my PostgreSQL databases. I can access the 
> default database (postgres), tablespace definitions, group roles and login 
> roles. The other database instance I have is the one that send me error 
> messages.
> 

To be clear you are running only one instance(cluster) of Postgres and 
that instance has several databases defined in it, correct?

If you go to PGDATA.pg_tblspc  do you have links to the tablespaces?

I only installed one instance of Postgres on my PC, which I use to manage two 
databases (postgres and osmdump). osmdump is the database that complains.

Regarding the links to the tablespaces, I first ran the following request.
SELECT spcname FROM pg_tablespace; and got the following list.
"pg_default"
"pg_global"
"workspace"
"datadrive1"
"datadrive2"
"datadrive3"
"datadrive0"

I went to E:\pgsqlData\pg_tblspc and found the links to the five last 
tablespaces above (I manually created them, the first two were created when I 
installed Postgres). 
E:\pgsqlData\pg_tblspc \113608\PG_9.3_201306121\18364 link to the 888 items 
mentioned previously (physically stored in K:\pgsqlData\pg_tblspc...)
I also found a PG_VERSION file in E:\pgsqlData. The file contains the value 9.3

Daniel

Re: Error message restarting a database

[Adrian Klaver]

On 1/27/19 2:22 PM, Begin Daniel wrote:

-Original Message-
From: Adrian Klaver [mailto:adrian.kla...@aklaver.com]
Sent: Sunday, January 27, 2019 16:52
To: Begin Daniel
Cc: pgsql-general
Subject: Re: Error message restarting a database

On 1/27/19 1:38 PM, Begin Daniel wrote:

-Original Message-
From: Adrian Klaver [mailto:adrian.kla...@aklaver.com]
Sent: Sunday, January 27, 2019 16:15
To: Begin Daniel
Cc: pgsql-general
Subject: Re: Error message restarting a database

On 1/27/19 12:56 PM, Begin Daniel wrote:

Not a good day, forgot to actually Cc list last time.


-Original Message-
From: Adrian Klaver [mailto:adrian.kla...@aklaver.com]
Sent: Sunday, January 27, 2019 15:43
To: Begin Daniel
Subject: Re: Error message restarting a database

On 1/27/19 12:38 PM, Begin Daniel wrote:
Please reply to list also
Ccing list.

-Original Message-
From: Adrian Klaver [mailto:adrian.kla...@aklaver.com]
Sent: Sunday, January 27, 2019 15:33
To: Begin Daniel; pgsql-general@lists.postgresql.org
Subject: Re: Error message restarting a database

On 1/27/19 12:23 PM, Begin Daniel wrote:

I worked with a PostgreSQL database (9.3) for a couple of years on my
PC. I shut down the PostgreSQL server about a year ago and I obviously
did not access the database since. Now I am trying to access again to
the database but I get the following error message...

FATAL: *"pg_tblspc/113608/PG. 9.3_ 201306121/18364" is not a valid data
directory

DETAIL: File *" pg_tblspc/113608/PG. 9.3_ 201306121/18364/PG_VERSION" is
missing.

Anyone has an idea on how to solve this problem?


What OS and version?
Best guess is your OS updated Postgres in the past year and the newer
version is trying to load an older data directory.



Daniel



OS and version...
Windows 10 Pro, Version 1809 installed on 2019-01-11
OS Build 17763.253


Whoops, just realized the errors are pointing at a tablespace/dir.

Does the directory in the error message exist?





I have multiple tablespaces. According to the definition of my tablespaces, the 
error point on the following directory:  K:\pgsqlData\PG_9.3_201306121\18364
The directory exists with 888 items in it, but there is no file named PG_VERSION



Are all the tablespaces connected to a single Postgres instance?

Are you still using Postgres 9.3?

Does the  Postgres top level data directory still exist?


Right, not a good day ;-)
- All tablespaces are connected to a single Postgres database instance
- I am still using Postgres 9.3 since I did not change anything related to 
PostgreSQL since I shut down the server a year ago
- I am not sure what you mean by top level directory.


PGDATA from here:
https://www.postgresql.org/docs/10/storage-file-layout.html

I am going to say PGDATA exists given the below.




I am using PgAdmin to access my PostgreSQL databases. I can access the default 
database (postgres), tablespace definitions, group roles and login roles. The 
other database instance I have is the one that send me error messages.



To be clear you are running only one instance(cluster) of Postgres and
that instance has several databases defined in it, correct?

If you go to PGDATA.pg_tblspc  do you have links to the tablespaces?

I only installed one instance of Postgres on my PC, which I use to manage two 
databases (postgres and osmdump). osmdump is the database that complains.

Regarding the links to the tablespaces, I first ran the following request.
SELECT spcname FROM pg_tablespace; and got the following list.
"pg_default"
"pg_global"
"workspace"
"datadrive1"
"datadrive2"
"datadrive3"
"datadrive0"

I went to E:\pgsqlData\pg_tblspc and found the links to the five last 
tablespaces above (I manually created them, the first two were created when I 
installed Postgres).
E:\pgsqlData\pg_tblspc \113608\PG_9.3_201306121\18364 link to the 888 items 
mentioned previously (physically stored in K:\pgsqlData\pg_tblspc...)


Hmm, K:\pgsqlData\pg_tblspc looks suspiciously like something 
masquerading as another PGDATA directory. Does a directory listing show 
what is shown in?:


https://www.postgresql.org/docs/10/storage-file-layout.html


I also found a PG_VERSION file in E:\pgsqlData. The file contains the value 9.3

Daniel




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

RE: Error message restarting a database

[Begin Daniel]

-Original Message-
From: Adrian Klaver [mailto:adrian.kla...@aklaver.com] 
Sent: Sunday, January 27, 2019 17:38
To: Begin Daniel
Cc: pgsql-general
Subject: Re: Error message restarting a database

On 1/27/19 2:22 PM, Begin Daniel wrote:
> -Original Message-
> From: Adrian Klaver [mailto:adrian.kla...@aklaver.com]
> Sent: Sunday, January 27, 2019 16:52
> To: Begin Daniel
> Cc: pgsql-general
> Subject: Re: Error message restarting a database
> 
> On 1/27/19 1:38 PM, Begin Daniel wrote:
>> -Original Message-
>> From: Adrian Klaver [mailto:adrian.kla...@aklaver.com]
>> Sent: Sunday, January 27, 2019 16:15
>> To: Begin Daniel
>> Cc: pgsql-general
>> Subject: Re: Error message restarting a database
>>
>> On 1/27/19 12:56 PM, Begin Daniel wrote:
>>
>> Not a good day, forgot to actually Cc list last time.
>>
>>> -Original Message-
>>> From: Adrian Klaver [mailto:adrian.kla...@aklaver.com]
>>> Sent: Sunday, January 27, 2019 15:43
>>> To: Begin Daniel
>>> Subject: Re: Error message restarting a database
>>>
>>> On 1/27/19 12:38 PM, Begin Daniel wrote:
>>> Please reply to list also
>>> Ccing list.
 -Original Message-
 From: Adrian Klaver [mailto:adrian.kla...@aklaver.com]
 Sent: Sunday, January 27, 2019 15:33
 To: Begin Daniel; pgsql-general@lists.postgresql.org
 Subject: Re: Error message restarting a database

 On 1/27/19 12:23 PM, Begin Daniel wrote:
> I worked with a PostgreSQL database (9.3) for a couple of years on my
> PC. I shut down the PostgreSQL server about a year ago and I obviously
> did not access the database since. Now I am trying to access again to
> the database but I get the following error message...
>
> FATAL: *"pg_tblspc/113608/PG. 9.3_ 201306121/18364" is not a valid data
> directory
>
> DETAIL: File *" pg_tblspc/113608/PG. 9.3_ 201306121/18364/PG_VERSION" is
> missing.
>
> Anyone has an idea on how to solve this problem?

 What OS and version?
 Best guess is your OS updated Postgres in the past year and the newer
 version is trying to load an older data directory.

>
> Daniel
>

 OS and version...
 Windows 10 Pro, Version 1809 installed on 2019-01-11
 OS Build 17763.253
>>>
>>> Whoops, just realized the errors are pointing at a tablespace/dir.
>>>
>>> Does the directory in the error message exist?
>>>


>>> I have multiple tablespaces. According to the definition of my tablespaces, 
>>> the error point on the following directory:  
>>> K:\pgsqlData\PG_9.3_201306121\18364
>>> The directory exists with 888 items in it, but there is no file named 
>>> PG_VERSION
>>>
>>
>> Are all the tablespaces connected to a single Postgres instance?
>>
>> Are you still using Postgres 9.3?
>>
>> Does the  Postgres top level data directory still exist?
>>
>>
>> Right, not a good day ;-)
>> - All tablespaces are connected to a single Postgres database instance
>> - I am still using Postgres 9.3 since I did not change anything related to 
>> PostgreSQL since I shut down the server a year ago
>> - I am not sure what you mean by top level directory.
> 
> PGDATA from here:
> https://www.postgresql.org/docs/10/storage-file-layout.html
> 
> I am going to say PGDATA exists given the below.
> 
> 
>>
>> I am using PgAdmin to access my PostgreSQL databases. I can access the 
>> default database (postgres), tablespace definitions, group roles and login 
>> roles. The other database instance I have is the one that send me error 
>> messages.
>>
> 
> To be clear you are running only one instance(cluster) of Postgres and
> that instance has several databases defined in it, correct?
> 
> If you go to PGDATA.pg_tblspc  do you have links to the tablespaces?
> 
> I only installed one instance of Postgres on my PC, which I use to manage two 
> databases (postgres and osmdump). osmdump is the database that complains.
> 
> Regarding the links to the tablespaces, I first ran the following request.
> SELECT spcname FROM pg_tablespace; and got the following list.
> "pg_default"
> "pg_global"
> "workspace"
> "datadrive1"
> "datadrive2"
> "datadrive3"
> "datadrive0"
> 
> I went to E:\pgsqlData\pg_tblspc and found the links to the five last 
> tablespaces above (I manually created them, the first two were created when I 
> installed Postgres).
> E:\pgsqlData\pg_tblspc \113608\PG_9.3_201306121\18364 link to the 888 items 
> mentioned previously (physically stored in K:\pgsqlData\pg_tblspc...)

Hmm, K:\pgsqlData\pg_tblspc looks suspiciously like something 
masquerading as another PGDATA directory. Does a directory listing show 
what is shown in?:

https://www.postgresql.org/docs/10/storage-file-layout.html

> I also found a PG_VERSION file in E:\pgsqlData. The file contains the value 
> 9.3
> 
> Daniel
> 

Sorry, bad copy paste, you should have read K:\pgsqlData\PG_9.3_201306121\18364
The suggested content is found only in E:\pgsqlData, including PG_VERSION file 
and pg_t

Re: Does creating readOnly connections, when possible, free up resources in Postgres?

[Hannes Erven]

Hi David,


> I saw that when you create a JDBC connection, you can specify
> readOnly=true. Would doing so somehow help Postgres manage its other
> connections?


if you know that a certain connection will be ready-only, you could use 
a more aggressive pooling strategy.


Usually, a connection pool will return a worker thread to the pool when 
the client closes the connection.
pgbouncer for example offers a "statement" pooling strategy, meaning 
that a worker connection is returned to the pool after every completed 
SQL statement.
That way, a single Postgresql server thread can easily serve many client 
connections.
If a specific usecase fits into a single command, you can even use it 
for these writes!


Of course, you can't use server-side prepared statements, temporary 
tables, ... or anything else that depends on subsequent statements 
hitting the same server process.



For an application I'm running, we have dozens of GUI clients keeping a 
JDBC connection open all day checking for updates, and with statement 
pooling this is handled well by 1-3 server processes (auto-scaled by 
pgbouncer).



Best regards,

-hannes

Re: Query help

[Chuck Martin]

On Sun, Jan 27, 2019 at 5:27 PM Chuck Martin 
wrote:

> On Sun, Jan 27, 2019 at 8:07 AM Peter J. Holzer  wrote:
>
>> On 2019-01-26 18:04:23 -0500, Chuck Martin wrote:
>> [snip]
>> > The idea should be obvious, but to explain, insdatetime is set when a
>> new
>> > record is created in any table. All records in ombcase have a foreign
>> key to
>> > status that can't be null. When status changes, a record is created in
>> > statuschange recording the old and new status keys, and the time (etc).
>> >
>> > The goal is to find records in ombcase that have not had a status
>> change in xx
>> > days. If the status has not changed, there will be no statuschange
>> record.
>>
>> The easiest way is to use set operations:
>>
>> select case_pkey from ombcase;
>> gives you all the ombcase ids.
>>
>> select ombcase_fkey from statuschange where insdatetime >= now()::date -
>> xx;
>> gives you all ombcase ids which had a status change in the last xx days.
>>
>> Therefore,
>> select case_pkey from ombcase
>> except
>> select ombcase_fkey from statuschange where insdatetime >= now()::date -
>> xx;
>> gives you all ombcase ids which did /not/ have a status change in the
>> last xx days.
>>
>
> I was not familiar with set operations, but studied up a bit and thought I
> was getting there. Not quite, though. I have two queries that individually
> return 1) all ombcase records with no statuschange record, and 2) the
> newest statuschange record for each case that has a statuschange record.
> But just putting UNION between then doesn't work. Here are my queries:
>
> --First, find all open cases with no statuschange record
> SELECT
>
> case_pkey,statuschange_pkey,case_fkey,ombcase.insdatetime,statuschange.insdatetime
> FROM
> ombcase
> LEFT JOIN
> statuschange
> ON
>   statuschange.case_fkey = case_pkey
>   AND case_pkey <> 0
> LEFT JOIN
> status
>  ON status_fkey = status_pkey
>   WHERE lower(statusid) NOT LIKE  ('closed%')
>   AND statuschange.statuschange_pkey IS NULL
> UNION
>   --Now find the last status change record for each case that has one
>   SELECT DISTINCT ON (case_fkey)
>
> case_pkey,statuschange_pkey,case_fkey,ombcase.insdatetime,statuschange.insdatetime
> FROM
> statuschange,ombcase,status
> WHERE case_fkey = case_pkey
> AND status_fkey = status_pkey
> AND LOWER(statusid) NOT LIKE ('closed%')
> ORDER BY case_fkey, statuschange.insdatetime DESC
>
> If I run each part separately, I get the expected number of records. When I 
> combine them with UNION, I get "missing FROM-clause entry for table 
> "statuschange"
> So I'm very close here, and these two return the exact number of records I'm 
> expecting. So I just need to get them added together. Then I expect I can put 
> the whole thing in a WHERE clause with "AND ombcase.case_pkey IN ([the 
> combined results])"
>
> This was pretty easy to resolve. Putting parentheses around each half of
the query caused it to return the right results. Then I could reduce the
columns to just ombcase.case_pkey and use an IN statement. I think this
gets me where I need to be. I appreciate the help!

Chuck

Re: Error message restarting a database

[Adrian Klaver]

On 1/27/19 2:45 PM, Begin Daniel wrote:


If you go to PGDATA.pg_tblspc  do you have links to the tablespaces?

I only installed one instance of Postgres on my PC, which I use to manage two 
databases (postgres and osmdump). osmdump is the database that complains.

Regarding the links to the tablespaces, I first ran the following request.
SELECT spcname FROM pg_tablespace; and got the following list.
"pg_default"
"pg_global"
"workspace"
"datadrive1"
"datadrive2"
"datadrive3"
"datadrive0"

I went to E:\pgsqlData\pg_tblspc and found the links to the five last 
tablespaces above (I manually created them, the first two were created when I 
installed Postgres).
E:\pgsqlData\pg_tblspc \113608\PG_9.3_201306121\18364 link to the 888 items 
mentioned previously (physically stored in K:\pgsqlData\pg_tblspc...)


Hmm, K:\pgsqlData\pg_tblspc looks suspiciously like something
masquerading as another PGDATA directory. Does a directory listing show
what is shown in?:

https://www.postgresql.org/docs/10/storage-file-layout.html


I also found a PG_VERSION file in E:\pgsqlData. The file contains the value 9.3

Daniel



Sorry, bad copy paste, you should have read K:\pgsqlData\PG_9.3_201306121\18364
The suggested content is found only in E:\pgsqlData, including PG_VERSION file 
and pg_tblspc subdirectory



Your original post had:

FATAL: ·"pg_tblspc/113608/PG. 9.3_ 201306121/18364" is not a valid data 
directory


PG. 9.3_ 201306121/18364 does not look like PG_9.3_201306121\18364.
To me it looks like a case of corrupted symlink(or whatever it is called 
on Windows).


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

[ANN] pg2arrow

[Kohei KaiGai]

Hello,

I made a utility program to dump PostgreSQL database in Apache Arrow format.

Apache Arrow is a kind of data format for columnar-based structured
data; actively
developed by Spark and comprehensive communities.
It is suitable data representation for static and read-only but large
number of rows.
Many of data analytics tools support Apache Arrow as a common data
exchange format.
See, https://arrow.apache.org/

* pg2arrow
https://github.com/heterodb/pg2arrow

usage:
$ ./pg2arrow -h localhost postgres -c 'SELECT * FROM hogehoge LIMIT
1' -o /tmp/hogehoge.arrow
--> fetch results of the query, then write out "/tmp/hogehoge"
$ ./pg2arrow --dump /tmp/hogehoge
--> shows schema definition of the "/tmp/hogehoge"

$ python
>>> import pyarrow as pa
>>> X = pa.RecordBatchFileReader("/tmp/hogehoge").read_all()
>>> X.schema
id: int32
a: int64
b: double
c: struct
  child 0, x: int32
  child 1, y: double
  child 2, z: decimal(30, 11)
  child 3, memo: string
d: string
e: double
ymd: date32[day]

--> read the Apache Arrow file using PyArrow, then shows its schema definition.


It is also a groundwork for my current development - arrow_fdw; which
allows to scan
on the configured Apache Arrow file(s) as like regular PostgreSQL table.
I expect integration of the arrow_fdw support with SSD2GPU Direct SQL
of PG-Strom
can pull out maximum capability of the latest hardware (NVME and GPU).
Likely, it is an ideal configuration for log-data processing generated
by many sensors.

Please check it.
Comments, ideas, bug-reports, and other feedbacks are welcome.

As an aside, NVIDIA announced their RAPIDS framework; to exchange data frames
on GPU among multiple ML/Analytics solutions. It also uses Apache
Arrow as a common
format for data exchange, and this is also our groundwork for them.
https://www.nvidia.com/en-us/deep-learning-ai/solutions/data-science/

Thanks,
-- 
HeteroDB, Inc / The PG-Strom Project
KaiGai Kohei 

Re: Implementing an expanded object in C

[Andrew Gierth]

> "Michel" == Michel Pelletier  writes:

 Michel> Replying to my own problem here, I guess this was a situation
 Michel> where explaining it in detail revealed the problem to me. By
 Michel> specifying my type is 'passedbyvalue'

That cannot possibly be appropriate.

 Michel> CREATE TYPE matrix (
 Michel> internallength = 8,

Your type is clearly not a fixed-length type, because fixed-length types
cannot have expanded datums. A fixed-length type must contain its entire
representation within the fixed length - it is not allowed to be a
pointer to something else.

-- 
Andrew (irc:RhodiumToad)

Re: Error message restarting a database

[Begin Daniel]

Thank, I'll have a look

Sent from Galaxy S7


From: Adrian Klaver 
Sent: Sunday, January 27, 2019 8:18:08 PM
To: Begin Daniel
Cc: pgsql-general
Subject: Re: Error message restarting a database

On 1/27/19 2:45 PM, Begin Daniel wrote:

>> If you go to PGDATA.pg_tblspc  do you have links to the tablespaces?
>>
>> I only installed one instance of Postgres on my PC, which I use to manage 
>> two databases (postgres and osmdump). osmdump is the database that complains.
>>
>> Regarding the links to the tablespaces, I first ran the following request.
>> SELECT spcname FROM pg_tablespace; and got the following list.
>> "pg_default"
>> "pg_global"
>> "workspace"
>> "datadrive1"
>> "datadrive2"
>> "datadrive3"
>> "datadrive0"
>>
>> I went to E:\pgsqlData\pg_tblspc and found the links to the five last 
>> tablespaces above (I manually created them, the first two were created when 
>> I installed Postgres).
>> E:\pgsqlData\pg_tblspc \113608\PG_9.3_201306121\18364 link to the 888 items 
>> mentioned previously (physically stored in K:\pgsqlData\pg_tblspc...)
>
> Hmm, K:\pgsqlData\pg_tblspc looks suspiciously like something
> masquerading as another PGDATA directory. Does a directory listing show
> what is shown in?:
>
> https://www.postgresql.org/docs/10/storage-file-layout.html
>
>> I also found a PG_VERSION file in E:\pgsqlData. The file contains the value 
>> 9.3
>>
>> Daniel
>>
>
> Sorry, bad copy paste, you should have read 
> K:\pgsqlData\PG_9.3_201306121\18364
> The suggested content is found only in E:\pgsqlData, including PG_VERSION 
> file and pg_tblspc subdirectory
>

Your original post had:

FATAL: ·"pg_tblspc/113608/PG. 9.3_ 201306121/18364" is not a valid data
directory

PG. 9.3_ 201306121/18364 does not look like PG_9.3_201306121\18364.
To me it looks like a case of corrupted symlink(or whatever it is called
on Windows).

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