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

[Rene de Gek]

Item 3 from my previous message is resolved.

For some reason there was a line in the odbc.ini
"ConnSettings = prepareThreshold=0"
I think this is one of the things we have tried and forgot to remove.

After removing it, the application still reports "code: 999,
sqlerrortext: An invalid handle was passed in a call to the database
driver"

This is the new driver log.

[ab1ffb40]   mylog.c[logs_on_off]274: mylog_on=1 qlog_on=0
[ab1ffb40]connection[CC_connect]1069: entering...sslmode=disable
[ab1ffb40]connection[LIBPQ_CC_connect]1041: entering...
[ab1ffb40]connection[CC_initial_log]989: [QLOG]Driver
Version='12.00.,Oct 29 2019'
[ab1ffb40]connection[CC_initial_log]1024: DSN = 'PG12', server =
'thehostname', port = '5601', database = 'thedb', username =
'thedb_dbo', password='x'
[ab1ffb40]connection[LIBPQ_connect]2706: connecting to the database
using thehostname as the server and pqopt={}
[ab1ffb40]connection[LIBPQ_connect]2818: [QLOG]PQconnectdbParams:
host='thehostname' port='5601' dbname='thedb' user='thedb_dbo'
sslmode='disable' password='theDBO'
[ab1ffb40]connection[LIBPQ_connect]2857: libpq connection to the
database established.
[ab1ffb40]connection[LIBPQ_connect]2864: protocol=3
[ab1ffb40]connection[LIBPQ_connect]2871: Server version=12.0.2
[ab1ffb40]connection[LIBPQ_connect]2895: leaving 1
[ab1ffb40]connection[CC_send_query_append]1769: conn=0xab097fd0,
query='SET DateStyle = 'ISO';SET extra_float_digits = 2;show
transaction_isolation'
[ab1ffb40]connection[CC_send_query_append]1849: query_len=75
[ab1ffb40]connection[CC_send_query_append]1892: [QLOG]PQsendQuery:
0xab4f0968 'SET DateStyle = 'ISO';SET extra_float_digits = 2;show
transaction_isolation'
[ab1ffb40] qresult.c[QR_Constructor]173: entering
[ab1ffb40] qresult.c[QR_Constructor]242: leaving
[ab1ffb40]connection[CC_send_query_append]1934: [QLOG] ok: - 'C' - SET
[ab1ffb40]connection[CC_send_query_append]1950:  setting cmdbuffer = 'SET'
[ab1ffb40]connection[CC_send_query_append]2034:  returning res = 0xab4f0638
[ab1ffb40]connection[CC_send_query_append]1934: [QLOG] ok: - 'C' - SET
[ab1ffb40] qresult.c[QR_Constructor]173: entering
[ab1ffb40] qresult.c[QR_Constructor]242: leaving
[ab1ffb40]connection[CC_send_query_append]1950:  setting cmdbuffer = 'SET'
[ab1ffb40]connection[CC_send_query_append]2034:  returning res = 0xab4f0790
[ab1ffb40] qresult.c[QR_Constructor]173: entering
[ab1ffb40] qresult.c[QR_Constructor]242: leaving
[ab1ffb40]connection[CC_send_query_append]2073:  'T' no result_in: res
= 0xab4f5c78
[ab1ffb40] qresult.c[QR_from_PGresult]604: [QLOG] nFields: 1
[ab1ffb40] qresult.c[QR_set_num_fields]40: entering
[ab1ffb40] qresult.c[QR_set_num_fields]44: leaving
[ab1ffb40] qresult.c[QR_from_PGresult]635: [QLOG]
fieldname='transaction_isolation', adtid=25, adtsize=-1, atttypmod=-1
(rel,att)=(0,0)
[ab1ffb40] qresult.c[QR_prepare_for_tupledata]817: REALLOC: old_count
= 0, size = 0
[ab1ffb40] qresult.c[QR_read_tuples_from_pgres]1341: [QLOG] ok: - 'T' - SHOW
[ab1ffb40]connection[handle_show_results]1324: isolation 2 to be 0
[ab1ffb40] qresult.c[QR_Destructor]344: entering
[ab1ffb40] qresult.c[QR_close_result]255: entering
[ab1ffb40] qresult.c[QR_free_memory]479: entering fcount=0
[ab1ffb40] qresult.c[QR_free_memory]577: leaving
[ab1ffb40] qresult.c[QR_free_memory]479: entering fcount=0
[ab1ffb40] qresult.c[QR_free_memory]577: leaving
[ab1ffb40] qresult.c[QR_free_memory]479: entering fcount=1
[ab1ffb40] qresult.c[QR_free_memory]577: leaving
[ab1ffb40] qresult.c[QR_close_result]319: leaving
[ab1ffb40] qresult.c[QR_Destructor]348: leaving
[ab1ffb40]connection[CC_send_settings]2458: entering...
[ab1ffb40]connection[CC_lookup_lo]2518: entering...
[ab1ffb40]connection[CC_send_query_append]1769: conn=0xab097fd0,
query='select oid, typbasetype from pg_type where typname = 'lo''
[ab1ffb40]connection[CC_send_query_append]1849: query_len=57
[ab1ffb40]connection[CC_send_query_append]1892: [QLOG]PQsendQuery:
0xab4f0968 'select oid, typbasetype from pg_type where typname = 'lo''
[ab1ffb40] qresult.c[QR_Constructor]173: entering
[ab1ffb40] qresult.c[QR_Constructor]242: leaving
[ab1ffb40]connection[CC_send_query_append]2055: [QLOG] ok: - 'T' - SELECT 0
[ab1ffb40] qresult.c[QR_from_PGresult]604: [QLOG] nFields: 2
[ab1ffb40] qresult.c[QR_set_num_fields]40: entering
[ab1ffb40] qresult.c[QR_set_num_fields]44: leaving
[ab1ffb40] qresult.c[QR_from_PGresult]635: [QLOG] fieldname='oid',
adtid=26, adtsize=4, atttypmod=-1 (rel,att)=(1247,1)
[ab1ffb40] qresult.c[QR_from_PGresult]635: [QLOG]
fieldname='typbasetype', adtid=26, adtsize=4, atttypmod=-1
(rel,att)=(1247,25)
[ab1ffb40] qresult.c[QR_read_tuples_from_pgres]1341: [QLOG] ok: - 'T' - SELECT 0
[ab1ffb40] qresult.c[QR_Destructor]344: entering
[ab1ffb40] qresult.c[QR_close_result]255: entering
[ab1ffb40] qresult.c[QR_free_memory]479: entering fcount=0
[ab1ffb40] qresult.c[QR_free_memory]577: leaving
[ab1ffb40] qresult.c[QR_close_result]319: leaving
[ab1ffb40] qresult.c[QR_Destructor]348: leaving
[ab1ffb40]connection[

Re: BDR node status stays as C, not ready

[Vik Fearing]

On 7/1/20 8:14 AM, Emrah Tema wrote:
> I am trying to make multimaster replication but when I use bdr_init_copy
> the database is copied from remote node but the status of the node stays as
> C, not R so Ready and for this reason the replication is not made. What
> should I do?


BDR is proprietary software, so you should contact the vendor.
-- 
Vik Fearing

Re: PostgreSQL 12 - ERROR: could not rename file "pg_logical/snapshots/1A-7C00D890.snap.13594.tmp" to "pg_logical/snapshots/1A-7C00D890.snap": No space left on device

[Adrian Klaver]

On 6/30/20 10:51 PM, FOUTE K. Jaurès wrote:







  From /var/lib/postgresql/12/main/pg_logical what does du -sh  show?
Please show results as text, you can copy from terminal screen.

root@hybride:/var/lib/postgresql/12/main/pg_logical# du -sh
16K .



Has the problem occurred again?


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

Re: PostgreSQL 12 - ERROR: could not rename file "pg_logical/snapshots/1A-7C00D890.snap.13594.tmp" to "pg_logical/snapshots/1A-7C00D890.snap": No space left on device

[Alvaro Herrera]

On 2020-Jun-30, FOUTE K. Jaurès wrote:

> Hi everyone,
> 
> I am use a native logical replication in PostgreSQL 12 on Ubuntu 16.04 and
> all is working fine until i faced this error below.
> 
> ERROR:  could not rename file
> "pg_logical/snapshots/1A-7C00D890.snap.13594.tmp" to
> "pg_logical/snapshots/1A-7C00D890.snap": No space left on device

What are your fsync and wal_sync_method settings?

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

Re: Can't seem to mix an inner and outer join in a query and get it to work right.

[David Gauthier]

Thanks!

Actually, I want the outer join first.  If it finds something, then move on
to the inner join and filter out all those that don't join to a rec with
'autosmoke'.  But if the outer join does not connect to the workarea_env
table, then just return what you have (the purpose of the outer join)


On Mon, Jun 29, 2020 at 9:10 PM David G. Johnston <
david.g.johns...@gmail.com> wrote:

> On Monday, June 29, 2020, David Gauthier  wrote:
>
>>
>>sqf_id   | sqf_sl  |  as_cl  |
>> wa_id |   type
>>
>>
>> +-+-+---+---
>>
>> * arazhang_20.06.28-20:59:06_soc_navi24_main@4404799 | 4404957 | 4405363
>> |  2772 | autosmoke*
>>
>>  arazhang_20.06.28-20:59:06_soc_navi24_main@4404799 | 4404957 | 4405363
>> |  2773 |
>>
>>  arazhang_20.06.28-20:59:06_soc_navi24_main@4404799 | 4404957 | 4405363
>> |  2774 |
>>
>> * bgregory_20.06.29-09:46:49_raphael_main@1277530| 1277949 |
>> |   |*
>> (4 rows)
>>
>>
>> dvm.workarea_env on
>> dvdb-#
>> (sqf.sqf_runs.submitted_changelist=dvm.workarea_env.p4_changelist)
>> dvdb-#*inner join* dvm.dvm_events on
>> dvdb-#   (dvm.workarea_env.wa_id = dvm.dvm_events.wa_id
>> and dvm.dvm_events.type = 'autosmoke')
>> dvdb-# where sqf.sqf_Runs.submitted_shelvelist in (4404957,1277949);
>>sqf_id   | sqf_sl  |  as_cl  |
>> wa_id |   type
>>
>> +-+-+---+---
>>  arazhang_20.06.28-20:59:06_soc_navi24_main@4404799 | 4404957 | 4405363
>> |  2772 | autosmoke
>> (1 row)
>>
>>
>> Is there a way to retain the effect of that first outer join while
>> enforcing that inner join ?
>>
>
> The quick non-testable answer is most likely.  I usually end up doing
> trial-and-error and adding possibly unnecessary parentheses to force the
> needed order of operations (or moving part of the join into an explicit
> subquery, possibly using a CTE/WITH).  Right now it seems that the query is
> forcing, via the inner join, a final output where the column
> type=‘autosmoke’.  i.e., its doing the outer join first then the inner.
> You seem to want the reverse.
>
> David J.
>
>

Re: PostgreSQL 12 - ERROR: could not rename file "pg_logical/snapshots/1A-7C00D890.snap.13594.tmp" to "pg_logical/snapshots/1A-7C00D890.snap": No space left on device

[FOUTE K . Jaurès]

Le mer. 1 juil. 2020 à 15:45, Adrian Klaver  a
écrit :

> On 6/30/20 10:51 PM, FOUTE K. Jaurès wrote:
> >
> >
>
> >
> >   From /var/lib/postgresql/12/main/pg_logical what does du -sh  show?
> > Please show results as text, you can copy from terminal screen.
> >
> > root@hybride:/var/lib/postgresql/12/main/pg_logical# du -sh
> > 16K .
> >
>
> Has the problem occurred again?
>
Yes
2020-07-01 19: 19: 21.868 WAT [24204] ERROR: could not receive data from
WAL stream: ERROR: could not rename file "pg_logical / snapshots /
1E-8567EB0.snap.22195.tmp" to "pg_logical / snapshots / 1E-8567EB0.snap":
No space left on device

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


-- 
Jaurès FOUTE
Technology Consultant
ISNOV SARL - Business Technology Consulting
Tel: +237 79395671 / +237 96248793
Email: *jauresfo...@gmail.com *,
  jauresmelki...@yahoo.fr
Compte Skype: jauresmelkiore

Re: Can't seem to mix an inner and outer join in a query and get it to work right.

[David G. Johnston]

The convention here is to bottom post or inline responses.

On Wed, Jul 1, 2020 at 9:51 AM David Gauthier 
wrote:

> Actually, I want the outer join first.  If it finds something, then move
> on to the inner join and filter out all those that don't join to a rec with
> 'autosmoke'.  But if the outer join does not connect to the workarea_env
> table, then just return what you have (the purpose of the outer join)
>
>>
>>>
So your final result - ignoring columns - is basically:
(sqf, (workarea, events))

where either the entire (workarea, events) is null, or if it is
non-null then workarea must also be non-null

Thus: ((workarea is left joined against events) with the whole thing left
joined against sqf).  And we are back to the join ordering precedence since
what you originally wrote was ((sqf, workarea), events).

In short - two outer joins; you can work out precedence either with
syntactic order or parentheses.

David J.

Re: PostgreSQL 12 - ERROR: could not rename file "pg_logical/snapshots/1A-7C00D890.snap.13594.tmp" to "pg_logical/snapshots/1A-7C00D890.snap": No space left on device

[FOUTE K . Jaurès]

Le mer. 1 juil. 2020 à 17:11, Alvaro Herrera  a
écrit :

> On 2020-Jun-30, FOUTE K. Jaurčs wrote:
>
> > Hi everyone,
> >
> > I am use a native logical replication in PostgreSQL 12 on Ubuntu 16.04
> and
> > all is working fine until i faced this error below.
> >
> > ERROR:  could not rename file
> > "pg_logical/snapshots/1A-7C00D890.snap.13594.tmp" to
> > "pg_logical/snapshots/1A-7C00D890.snap": No space left on device
>
> What are your fsync and wal_sync_method settings?
>
All the two setting are commented
#fsync = on
#wal_sync_method = fsync


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


-- 
Jaurès FOUTE
Technology Consultant
ISNOV SARL - Business Technology Consulting
Tel: +237 79395671 / +237 96248793
Email: *jauresfo...@gmail.com *,
  jauresmelki...@yahoo.fr
Compte Skype: jauresmelkiore

Re: PostgreSQL 12 - ERROR: could not rename file "pg_logical/snapshots/1A-7C00D890.snap.13594.tmp" to "pg_logical/snapshots/1A-7C00D890.snap": No space left on device

[Adrian Klaver]

On 7/1/20 11:21 AM, FOUTE K. Jaurès wrote:






Has the problem occurred again?

Yes
2020-07-01 19: 19: 21.868 WAT [24204] ERROR: could not receive data from 
WAL stream: ERROR: could not rename file "pg_logical / snapshots / 
1E-8567EB0.snap.22195.tmp" to "pg_logical / snapshots / 
1E-8567EB0.snap": No space left on device


What are the permissions on?:

pg_logical/snapshots/





-- 
Adrian Klaver

adrian.kla...@aklaver.com 



--
Jaurès FOUTE
Technology Consultant
ISNOV SARL - Business Technology Consulting
Tel: +237 79395671 / +237 96248793
Email: _jauresfo...@gmail.com _,
jauresmelki...@yahoo.fr 
Compte Skype: jauresmelkiore



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

Re: PostgreSQL 12 - ERROR: could not rename file "pg_logical/snapshots/1A-7C00D890.snap.13594.tmp" to "pg_logical/snapshots/1A-7C00D890.snap": No space left on device

[Adrian Klaver]

On 7/1/20 11:21 AM, FOUTE K. Jaurès wrote:



Le mer. 1 juil. 2020 à 15:45, Adrian Klaver > a écrit :


On 6/30/20 10:51 PM, FOUTE K. Jaurès wrote:
 >
 >

 >
 >       From /var/lib/postgresql/12/main/pg_logical what does du
-sh  show?
 >     Please show results as text, you can copy from terminal screen.
 >
 > root@hybride:/var/lib/postgresql/12/main/pg_logical# du -sh
 > 16K .
 >

Has the problem occurred again?

Yes
2020-07-01 19: 19: 21.868 WAT [24204] ERROR: could not receive data from 
WAL stream: ERROR: could not rename file "pg_logical / snapshots / 
1E-8567EB0.snap.22195.tmp" to "pg_logical / snapshots / 
1E-8567EB0.snap": No space left on device


Also look in system log for any events around 2020-07-01 19: 19





-- 
Adrian Klaver

adrian.kla...@aklaver.com 



--
Jaurès FOUTE
Technology Consultant
ISNOV SARL - Business Technology Consulting
Tel: +237 79395671 / +237 96248793
Email: _jauresfo...@gmail.com _,
jauresmelki...@yahoo.fr 
Compte Skype: jauresmelkiore



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

Re: PostgreSQL 12 - ERROR: could not rename file "pg_logical/snapshots/1A-7C00D890.snap.13594.tmp" to "pg_logical/snapshots/1A-7C00D890.snap": No space left on device

[FOUTE K . Jaurès]

Le mer. 1 juil. 2020 à 22:15, Adrian Klaver  a
écrit :

> On 7/1/20 11:21 AM, FOUTE K. Jaurès wrote:
> >
>
> >
> > Has the problem occurred again?
> >
> > Yes
> > 2020-07-01 19: 19: 21.868 WAT [24204] ERROR: could not receive data from
> > WAL stream: ERROR: could not rename file "pg_logical / snapshots /
> > 1E-8567EB0.snap.22195.tmp" to "pg_logical / snapshots /
> > 1E-8567EB0.snap": No space left on device
>
> What are the permissions on?:
>
> pg_logical/snapshots/
>
> postgres
root@hybride:/var/lib/postgresql/12/main/pg_logical# ll
total 20
drwx--  4 postgres postgres 4096 juil.  1 20:21 ./
drwx-- 19 postgres postgres 4096 juil.  1 20:16 ../
drwx--  2 postgres postgres 4096 mars  31 11:58 mappings/
-rw---  1 postgres postgres   56 juil.  1 20:21 replorigin_checkpoint
drwx--  2 postgres postgres 4096 mars  31 11:58 snapshots/

>  >
> >
> >
> > --
> > Adrian Klaver
> > adrian.kla...@aklaver.com 
> >
> >
> >
> > --
> > Jaurès FOUTE
> > Technology Consultant
> > ISNOV SARL - Business Technology Consulting
> > Tel: +237 79395671 / +237 96248793
> > Email: _jauresfo...@gmail.com _,
> > jauresmelki...@yahoo.fr 
> > Compte Skype: jauresmelkiore
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>


-- 
Jaurès FOUTE
Technology Consultant
ISNOV SARL - Business Technology Consulting
Tel: +237 79395671 / +237 96248793
Email: *jauresfo...@gmail.com *,
  jauresmelki...@yahoo.fr
Compte Skype: jauresmelkiore

Different results from identical matviews

[Anders Steinlein]

Hi folks,

We have a materialized view from which a customer reported some
confusing/invalid results, leading us to inspect the query and not finding
anything wrong. Running the query defining the matview manually, or
creating a new (identical) materialized view returns the correct result.
Obviously, we've done REFRESH MATERIALIZED VIEW just before doing the
comparison, and all runs are in the same schema.

It's a pretty big query, but let's describe the two matviews to see that
they are identical. The first is the original returning invalid results,
the one with _2 name postfix is the re-created one.

mm_prod=> \d+ segments_with_contacts
   Materialized view "aakpnews.segments_with_contacts"
 Column | Type  | Collation | Nullable | Default | Storage  | Stats
target | Description
+---+---+--+-+--+--+-
 lid| integer   |   |  | | plain|
   |
 sid| integer   |   |  | | plain|
   |
 email  | public.citext |   |  | | extended |
   |
Indexes:
"segments_with_contacts_sid_lid_email_idx" UNIQUE, btree (sid, lid,
email)
View definition:
 WITH tagged_contacts AS (
 SELECT cl.lid,
cl.email,
cl.skip_preexisting_campaigns AS skip_subscribed,
ct.skip_preexisting_campaigns AS skip_tags,
cl.ladded,
ct.tagname,
ct.created
   FROM contacts_lists cl
 LEFT JOIN contacts_tags ct USING (email)
  WHERE cl.lstatus::bpchar = 'a'::bpchar
), tagged_segments AS (
 SELECT s.lid,
s.cid,
s.sid,
sp.type,
sp.mid,
sp.matchdelay,
sp.tagname,
sp.event,
count(*) OVER (PARTITION BY s.sid) AS requirements,
campaigns.activated_at
   FROM segments s
 LEFT JOIN campaigns USING (cid)
 JOIN segments_predicates sp USING (sid)
  WHERE s.archived_at IS NULL AND (s.cid IS NULL OR
campaigns.activated_at IS NOT NULL)
), segments_contacts AS (
 SELECT s.lid,
s.sid,
s.requirements,
CASE
WHEN s.type = 'subscribed'::public.predicate THEN (
SELECT array_agg(DISTINCT tagged_contacts.email::public.citext) AS array_agg
   FROM tagged_contacts
  WHERE tagged_contacts.lid = s.lid AND
tagged_contacts.ladded >= s.activated_at AND (s.matchdelay IS NULL OR
(tagged_contacts.ladded + s.matchdelay) < now()) AND NOT
COALESCE(tagged_contacts.skip_subscribed, false))
WHEN s.type = 'has_tag'::public.predicate THEN ( SELECT
array_agg(DISTINCT tagged_contacts.email::public.citext) AS array_agg
   FROM tagged_contacts
  WHERE tagged_contacts.lid = s.lid AND
tagged_contacts.tagname OPERATOR(public.=) s.tagname AND (s.matchdelay IS
NULL OR (tagged_contacts.created + s.matchdelay) < now()) AND (s.cid IS
NULL OR tagged_contacts.created >= s.activated_at AND NOT
COALESCE(tagged_contacts.skip_tags, false)))
WHEN s.type = 'not_has_tag'::public.predicate THEN (
SELECT array_agg(aggregated_tags.email::public.citext) AS array_agg
   FROM ( SELECT tagged_contacts.email,
array_agg(tagged_contacts.tagname) AS tags
   FROM tagged_contacts
  WHERE tagged_contacts.lid = s.lid AND (s.cid
IS NULL OR tagged_contacts.ladded >= s.activated_at AND NOT
COALESCE(tagged_contacts.skip_subscribed, false))
  GROUP BY tagged_contacts.email)
aggregated_tags
  WHERE NOT aggregated_tags.tags @> ARRAY[s.tagname])
WHEN s.type = 'received'::public.predicate THEN (
SELECT array_agg(DISTINCT mails_contacts_sent.email::public.citext) AS
array_agg
   FROM mails_contacts_sent
  WHERE mails_contacts_sent.mid = s.mid AND
(s.matchdelay IS NULL OR (mails_contacts_sent.senttime + s.matchdelay) <
now()))
WHEN s.type = 'not_received'::public.predicate THEN (
SELECT array_agg(x.email::public.citext) AS array_agg
   FROM ( SELECT tagged_contacts.email
   FROM tagged_contacts
  WHERE tagged_contacts.lid = s.lid
EXCEPT
 SELECT DISTINCT mails_contacts_sent.email
   FROM mails_contacts_sent
  WHERE mails_contacts_sent.mid = s.mid AND
(s.matchdelay IS NULL OR (mails_contacts_sent.senttime + s.matchdelay) <
now())) x)
WHEN s.type = 'opened'::public.predicate THEN ( SELECT
array_agg(DISTINCT mails_contacts_opens.email::public.ci

Re: PostgreSQL 12 - ERROR: could not rename file "pg_logical/snapshots/1A-7C00D890.snap.13594.tmp" to "pg_logical/snapshots/1A-7C00D890.snap": No space left on device

[Adrian Klaver]

On 7/1/20 2:21 PM, FOUTE K. Jaurès wrote:



Le mer. 1 juil. 2020 à 22:15, Adrian Klaver > a écrit :


On 7/1/20 11:21 AM, FOUTE K. Jaurès wrote:
 >

 >
 >     Has the problem occurred again?
 >
 > Yes
 > 2020-07-01 19: 19: 21.868 WAT [24204] ERROR: could not receive
data from
 > WAL stream: ERROR: could not rename file "pg_logical / snapshots /
 > 1E-8567EB0.snap.22195.tmp" to "pg_logical / snapshots /
 > 1E-8567EB0.snap": No space left on device

What are the permissions on?:

pg_logical/snapshots/

postgres
root@hybride:/var/lib/postgresql/12/main/pg_logical# ll
total 20
drwx--  4 postgres postgres 4096 juil.  1 20:21 ./
drwx-- 19 postgres postgres 4096 juil.  1 20:16 ../
drwx--  2 postgres postgres 4096 mars  31 11:58 mappings/
-rw---  1 postgres postgres   56 juil.  1 20:21 replorigin_checkpoint
drwx--  2 postgres postgres 4096 mars  31 11:58 snapshots/



Well replication is between two instances, so are you sure you are 
looking at the correct data directory?





--
Jaurès FOUTE
Technology Consultant
ISNOV SARL - Business Technology Consulting
Tel: +237 79395671 / +237 96248793
Email: _jauresfo...@gmail.com _,
jauresmelki...@yahoo.fr 
Compte Skype: jauresmelkiore



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

Re: Different results from identical matviews

[Tom Lane]

Anders Steinlein  writes:
> We have a materialized view from which a customer reported some
> confusing/invalid results, leading us to inspect the query and not finding
> anything wrong. Running the query defining the matview manually, or
> creating a new (identical) materialized view returns the correct result.
> Obviously, we've done REFRESH MATERIALIZED VIEW just before doing the
> comparison, and all runs are in the same schema.

I suspect the query underlying the matviews is less deterministic than
you think it is.  I did not study that query in any detail, but just
from a quick eyeball: the array_agg() calls with no attempt to enforce a
particular aggregation order are concerning, and so is grouping by
a citext column (where you'll get some case-folding of a common value,
but who knows which).

regards, tom lane

Re: PostgreSQL 12 - ERROR: could not rename file "pg_logical/snapshots/1A-7C00D890.snap.13594.tmp" to "pg_logical/snapshots/1A-7C00D890.snap": No space left on device

[raf]

FOUTE K. Jaurès wrote:

> Le mer. 1 juil. 2020 à 00:11, raf  a écrit :
> 
> > FOUTE K. Jaurčs wrote:
> >
> > > Hi everyone,
> > >
> > > I am use a native logical replication in PostgreSQL 12 on Ubuntu 16.04
> > and
> > > all is working fine until i faced this error below.
> > >
> > > ERROR:  could not rename file
> > > "pg_logical/snapshots/1A-7C00D890.snap.13594.tmp" to
> > > "pg_logical/snapshots/1A-7C00D890.snap": No space left on device
> > >
> > > Is there someone who faced this problem?
> > > Any idea how I can solve it ?
> > >
> > > BTW: I don't have any problem with space.
> > >
> > > Best Regard
> > >
> > > --
> > > Jaurčs FOUTE
> >
> > If you really haven't run out of space,
> > you might have run out of inodes.
> > The -i option of df should show you.
> > 100,000 empty files could cause that.
> > I wouldn't think that renaming would
> > require a new inode, but I also wouldn't
> > think that renaming would require any
> > more space on a file system.
> 
> 
> The result of df -i
> 
> Sys. de fichiers   Inœuds IUtil.   ILibre IUti% Monté sur
> udev  3065149433  30647161% /dev
> tmpfs 3072780665  30721151% /run
> /dev/sdb259973632 356029 596176031% /
> tmpfs 3072780  7  30727731% /dev/shm
> tmpfs 3072780 10  30727701% /run/lock
> tmpfs 3072780 17  30727631% /sys/fs/cgroup
> /dev/sdb1   0  00 - /boot/efi
> tmpfs 3072780 19  30727611% /run/user/108
> tmpfs 3072780  5  30727751% /run/user/1001
> tmpfs 3072780  5  30727751% /run/user/1000

So that's not it. It would take ~60 million
files to fill up your / inode table. I can't
think of another explanation for that error
message if df without -i also shows free space.

I tried googling for "No space left on device"
and everything suggested checking inodes with "df -i".
One suggested looking for files that were deleted
but that were still open by a process (can be found
with "lsof / | grep deleted") but if that were the
problem, df would show a full disk (but du wouldn't
be able to account for it).

Could it be some other "device"? like shared memory
segment space or IPC resources? That seems unlikely if
the operation was just a file system rename. Perhaps
strace/stap could help check if it really was a file
system rename that went wrong (if there's any reason to
doubt it). It does seem to clearly be a file rename
though.

If there any chance that the drive is failing? But
you'd expect "I/O error" messages if that were the case.

Is it on a journalling file system and could the journal
be the device that is full? I don't know how to check
that (or if it even makes sense).

cheers,
raf

Re: PostgreSQL 12 - ERROR: could not rename file "pg_logical/snapshots/1A-7C00D890.snap.13594.tmp" to "pg_logical/snapshots/1A-7C00D890.snap": No space left on device

[Tom Lane]

raf  writes:
> FOUTE K. Jaurès wrote:
>> The result of df -i
>> 
>> Sys. de fichiers   Inœuds IUtil.   ILibre IUti% Monté sur
>> udev  3065149433  30647161% /dev
>> tmpfs 3072780665  30721151% /run
>> /dev/sdb259973632 356029 596176031% /
>> tmpfs 3072780  7  30727731% /dev/shm
>> tmpfs 3072780 10  30727701% /run/lock
>> tmpfs 3072780 17  30727631% /sys/fs/cgroup
>> /dev/sdb1   0  00 - /boot/efi
>> tmpfs 3072780 19  30727611% /run/user/108
>> tmpfs 3072780  5  30727751% /run/user/1001
>> tmpfs 3072780  5  30727751% /run/user/1000

> So that's not it. It would take ~60 million
> files to fill up your / inode table. I can't
> think of another explanation for that error
> message if df without -i also shows free space.

I'm going to take a shot in the dark and ask if the root file system
is XFS.  It doesn't take too much googling to find out that XFS has a
reputation for reporting ENOSPC when there seems to be more than enough
room left.  Apparently, it can do so as a result of fragmentation problems
even though the disk as a whole has lots of space --- for one cautionary
example see this thread:

https://www.spinics.net/lists/linux-xfs/msg22856.html

typo-ishly titled "ENSOPC on a 10% used disk".  It looks like the XFS crew
installed a fix for the underlying bug ... but that thread is from 2018
and you're running a 2015 Ubuntu release.

Or, to cut to the chase: maybe updating to a less hoary kernel would help.
If you are stuck with this Ubuntu release for some reason, consider using
a less bleeding-edge-at-the-time file system.

regards, tom lane

If crashes during VACUUM, will it recover from a checkpoint that is before the forzen transaction ID of VACUUM?

[Jialun Zhang]

Hello All,

When VACUUM starts, it will calculate a frozen transaction ID and freeze
all tuples older than it. Will this frozen TXID younger than the latest
checkpoint?

If this is the case, then when the system crashes after VACUUM has deleted
some dead tuples and the system recovers from this checkpoint, these tuples
will be available to some transactions again during the replaying of WAL.

I am new to Postgres, hope my understanding is not too wrong.

Thanks,
Reatank

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

[Rene de Gek]

Hi Adrian,

Thank you for your reply. This was one of the things that Matthias has
also suggested
to us, referring to
https://sybase.public.easerver.powerbuilder.narkive.com/MsoAXSQJ/problem-connecting-to-database-from-easerver-component
.

We do set the server type to ODBC.

The error 999 shows up with several error messages. For example:

When we connect to PostgreSQL using ODBC but we forget to fill in sqlca.DBMS,
the application log has 999 without an errortext:

fnv_connect: -1
DBHandle is 0
Setting up n_tr
DBMS:
DBParm: CacheName='pgsqlODBC12'
Autocommit: false
connect using i_tr...
dberror event triggered on n_tr
code: 999
sqlerrortext:
sqlsyntax:
SQLErrText:
sqldbcode: 999
sqlcode: -1

When we try to connect to ODBC, but we set sqlca.DBMS incorrectly to JDBC,
the code is 999, but there is a message "sqlerrortext: Java Exception :
Fatal Error. Unable to initialize DatabaseMetaData class."

fnv_connect: -1
DBHandle is 0
Setting up n_tr
DBMS: JDBC
DBParm: CacheName='pgsqlODBC12'
Autocommit: false
connect using i_tr...
dberror event triggered on n_tr
code: 999
sqlerrortext: Java Exception : Fatal Error. Unable to initialize
DatabaseMetaData class.
sqlsyntax:
SQLErrText: Java Exception : Fatal Error. Unable to initialize
DatabaseMetaData class.
sqldbcode: 999
sqlcode: -1

When connecting to ODBC using the correct sqlca.DBMS=ODBC, we also get the 999,
but with the errortext about the invalid handle.

fnv_connect: -1
DBHandle is 0
Setting up n_tr
DBMS: ODBC
DBParm: CacheName='pgsqlODBC12'
Autocommit: false
connect using i_tr...
dberror event triggered on n_tr
code: 999
sqlerrortext: An invalid handle was passed in a call to the database driver.
sqlsyntax:
SQLErrText: An invalid handle was passed in a call to the database driver.
sqldbcode: 999
sqlcode: -1

Unfortunately, it seems that the error 999 can have several causes.

On the EAServer side we have tried all kind of things.
If the connection log from the driver looks okay I am afraid we have ran out
of ideas.

Adrian Klaver wrote:

>
> A quick search found that this code be returned if the correct server
> type is not specified in EAServer.
>
>

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

[Virendra Kumar]

System level call trace might help little bit using strace/truss utility.

Regards,
Virendra Kumar
On Wednesday, July 1, 2020, 9:26:09 PM PDT, Rene de Gek 
 wrote:  
 
 Hi Adrian,

Thank you for your reply. This was one of the things that Matthias has also 
suggested to us, referring to
https://sybase.public.easerver.powerbuilder.narkive.com/MsoAXSQJ/problem-connecting-to-database-from-easerver-component.

We do set the server type to ODBC.

The error 999 shows up with several error messages. For example:

When we connect to PostgreSQL using ODBC but we forget to fill in sqlca.DBMS, 
the application log has 999 without an errortext:

fnv_connect: -1DBHandle is 0Setting up n_tr
DBMS:
DBParm: CacheName='pgsqlODBC12'
Autocommit: false
connect using i_tr...
dberror event triggered on n_tr
code: 999
sqlerrortext:
sqlsyntax:
SQLErrText:
sqldbcode: 999
sqlcode: -1

When we try to connect to ODBC, but we set sqlca.DBMS incorrectly to JDBC, the 
code is 999, but there is a message "sqlerrortext: Java Exception : Fatal 
Error. Unable to initialize DatabaseMetaData class."

fnv_connect: -1DBHandle is 0
Setting up n_tr
DBMS: JDBCDBParm: CacheName='pgsqlODBC12'
Autocommit: false
connect using i_tr...
dberror event triggered on n_tr
code: 999
sqlerrortext: Java Exception : Fatal Error. Unable to initialize
DatabaseMetaData class.
sqlsyntax:
SQLErrText: Java Exception : Fatal Error. Unable to initialize
DatabaseMetaData class.
sqldbcode: 999
sqlcode: -1

When connecting to ODBC using the correct sqlca.DBMS=ODBC, we also get the 999, 
but with the errortext about the invalid handle.

fnv_connect: -1DBHandle is 0
Setting up n_tr
DBMS: ODBC
DBParm: CacheName='pgsqlODBC12'
Autocommit: false
connect using i_tr...
dberror event triggered on n_tr
code: 999
sqlerrortext: An invalid handle was passed in a call to the database driver.
sqlsyntax:
SQLErrText: An invalid handle was passed in a call to the database driver.
sqldbcode: 999
sqlcode: -1

Unfortunately, it seems that the error 999 can have several causes.

On the EAServer side we have tried all kind of things.
If the connection log from the driver looks okay I am afraid we have ran out of 
ideas.

Adrian Klaver wrote:


A quick search found that this code be returned if the correct server 
type is not specified in EAServer.