Re: [GENERAL] Python versus Other Languages using PostgreSQL

[Dick Kniep]

As a long time user of the combination, Postgresql, Python, Django, 
Flask etc. here are my 2 cents:


The frameworks of python; Django (enormous) or Flask (smaller) and there 
are more, are of superb quality, battle hardened and are used in many 
many companies and high volume sites around the world. The rigid 
implementation of the mvc concept makes for readable, maintainable code. 
This is far far more important than execution speed, because:


- most modern application use databases where the IO time is always much 
much more important than the speed of processing.


- The power (enormous) of the current hardware is such that extra cycles 
do not cost much


- Price (virtually nothing) of the current hardware allows for swift 
adding of extra hardware if necessary


- Price of developers is very very high, so any gain in development 
speed directly returns to the company as MUCH less expense


- The reliability of the Postgresql has always been a very strong point 
(I myself use it since 2002 and have had hardly any problems with it) 
even under bad circumstances. The loss of data because of a malfunction 
of the database does simply not occur. This comes at a cost (raw speed), 
but the cost of loosing data is much much higher than a little extra 
hardware.


Cheers


On 09-05-17 16:48, Francisco Olarte wrote:

On Tue, May 9, 2017 at 1:44 PM, vinny  wrote:

In fact, I don't think many companies/developers even choose a language
or database, but rather just use whatever they have experience in.

That is choosing. You choose them because you know them.


Francisco Olarte.






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

[GENERAL] Strange Error in postgresql 8.4

[Dick Kniep]

Hi list,

We have been using Postgresql for more than a decade now. Never had any real 
problems. Thank you very much for this great product.

Now recently I have noticed a strange problem.

Below is a traceback of a python program using psycopg and this correlates with 
the postgresql log below that.

Traceback (most recent call last):
  File /opt/CVix/src/ftsDocs.py, line 686, in module
CVixfts(schemanaam=schemanaam, hostnaam=hostnaam, dbnaam=dbnaam, 
production=production)
  File /opt/CVix/src/ftsDocs.py, line 82, in __init__
dbhost=hostnaam, dbname=dbnaam, production=production)
  File /opt/CVix-prod/src/Batch/BatchMod.py, line 58, in __init__
self.ExecPgm(schemanaam, programma, importfile)
  File /opt/CVix-prod/src/Batch/BatchMod.py, line 69, in ExecPgm
else: eval('self.' + programma + '(schemanaam)')
  File string, line 1, in module
  File /opt/CVix/src/ftsDocs.py, line 124, in BuildFtsIndex
self.indexDocs()
  File /opt/CVix/src/ftsDocs.py, line 202, in indexDocs
docs, cursorContext = self.DocTAB.scroll(WHERE=wherestmt, LIMIT=50, 
withhold=True)
  File /opt/CVix-prod/src/SQLConnect/SQLDict.py, line 620, in scroll
c = self.select(WHERE=WHERE, LOGICALDELETE=LOGICALDELETE, ORDER=ORDER, 
NAMEDCURSOR=True, WITHHOLD=withhold)
  File /opt/CVix-prod/src/SQLConnect/SQLDict.py, line 599, in select
if self.executeSQL(c, selectstmt, i): return self._resultcursor
  File /opt/CVix-prod/src/SQLConnect/SQLDict.py, line 309, in executeSQL
self._resultcursor.execute(stmt)
psycopg2.DatabaseError: SSL error: ccs received early

postresql log

2014-06-20 09:45:09 CEST LOG:  duration: 4265.211 ms  statement: SELECT 
doc_id, document, mutd
at, mutmed, opd_id, rapport, doc_type, cli_id, classificatie, 
entry_owner, createda
te, doc_ptype, bed_id, cper_id, lastrealuser, doc_mimetype, 
doc_md5sum, doc_selectman
ually, eigen_bed_id, doc_template, per_id, doc_standardtype, 
pathfilename, lastmodifica
tion, imported, doc_fts_processed, doc_address_addition, doc_type_id 
FROM comsupport.do
cumenten  sqd1  WHERE (doc_fts_processed  mutdat or doc_fts_processed is 
NULL) 
2014-06-20 09:45:31 CEST LOG:  could not send data to client: Connection reset 
by peer
2014-06-20 09:45:31 CEST STATEMENT:  SELECT doc_id, document, mutdat, 
mutmed, opd_id, rapport, doc_type, cli_id, classificatie, 
entry_owner, createdate, doc_ptype, bed_id, cper_id, lastrealuser, 
doc_mimetype, doc_md5sum, doc_selectmanually, eigen_bed_id, 
doc_template, doc_standardtype, per_id, pathfilename, 
lastmodification, doc_fts_processed, imported, doc_address_addition, 
doc_type_id FROM menea.documenten  sqd1  WHERE (doc_fts_processed  
mutdat or doc_fts_processed is NULL) 
2014-06-20 09:45:31 CEST LOG:  could not send data to client: Broken pipe
2014-06-20 09:45:31 CEST STATEMENT:  SELECT doc_id, document, mutdat, 
mutmed, opd_id, rapport, doc_type, cli_id, classificatie, 
entry_owner, createdate, doc_ptype, bed_id, cper_id, lastrealuser, 
doc_mimetype, doc_md5sum, doc_selectmanually, eigen_bed_id, 
doc_template, doc_standardtype, per_id, pathfilename, 
lastmodification, doc_fts_processed, imported, doc_address_addition, 
doc_type_id FROM menea.documenten  sqd1  WHERE (doc_fts_processed  
mutdat or doc_fts_processed is NULL) 
2014-06-20 09:45:33 CEST LOG:  duration: 16535.228 ms  statement: SELECT 
doc_id, document, mutdat, mutmed, opd_id, rapport, doc_type, 
cli_id, classificatie, entry_owner, createdate, doc_ptype, bed_id, 
cper_id, lastrealuser, doc_mimetype, doc_md5sum, doc_selectmanually, 
eigen_bed_id, doc_template, doc_standardtype, per_id, pathfilename, 
lastmodification, doc_fts_processed, imported, doc_address_addition, 
doc_type_id FROM menea.documenten  sqd1  WHERE (doc_fts_processed  
mutdat or doc_fts_processed is NULL) 
2014-06-20 09:45:33 CEST LOG:  SSL error: bad write retry
2014-06-20 09:45:33 CEST LOG:  could not send data to client: Connection reset 
by peer
2014-06-20 09:45:33 CEST LOG:  SSL error: sslv3 alert unexpected message
2014-06-20 09:45:33 CEST LOG:  could not receive data from client: Connection 
reset by peer
2014-06-20 09:45:33 CEST LOG:  unexpected EOF on client connection

Googling did not reveal any clues on what is causing this. I am considering a 
reboot of the complete db server, but I don't like to do things without having 
a clue on what is causing this.

Cheers,
D. Kniep


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

Re: [GENERAL] Shit happens

[Dick Kniep]

Hi list,

Thanks for the clear answer. However, this is the simple answer that is also in 
the manual. Yes I know it is not directly possible to get that data, but I am 
quite desparate to get the data back. If one way or another the data is (except 
for the 4 days we really have no data for) accessible, we will write a program 
to recover the data into the production database. So if anyone of you knows 
about a way to access the actual data in the WAL file (or a reference where to 
find enough information to do this) I would be very happy.


Cheers,
D. Kniep


-Oorspronkelijk bericht-
Van: Andreas Kretschmer akretsch...@spamfence.net
Verzonden: za 01-01-11 10:04:11
Aan: pgsql-general@postgresql.org; 
Onderwerp: Re: [GENERAL] Shit happens

 Dick Kniep dick.kn...@lindix.nl wrote:
 
  Hi list,
  
  Happy newyear to all of you.
  
  We use WAL files for backup purposes. Now we need to restore up to a
  certain date in december, we have a base backup from october 24th, but
  the WAL files start only at october 28th. Is there a way that we can
  use the WAL files to recover data up to half december anyway? I know
 
 No. You can't fill the gap.
 
 
 Andreas
 -- 
 Really, I'm not out to destroy Microsoft. That will just be a completely
 unintentional side effect.  (Linus Torvalds)
 If I was god, I would recompile penguin with --enable-fly.   (unknown)
 Kaufbach, Saxony, Germany, Europe.  N 51.05082°, E 13.56889°
 
 -- 
 Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
 To make changes to your subscription:
 http://www.postgresql.org/mailpref/pgsql-general
 
 !DSPAM:4d1eee0b160842018316037!
 


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

Re: [GENERAL] Shit happens

[Dick Kniep]

Hi list,

Thanks for the clear answer. However, this is the simple answer that is also in 
the manual. Yes I know it is not directly possible to get that data, but I am 
quite desparate to get the data back. If one way or another the data is (except 
for the 4 days we really have no data for) accessible, we will write a program 
to recover the data into the production database. So if anyone of you knows 
about a way to access the actual data in the WAL file (or a reference where to 
find enough information to do this) I would be very happy.


Cheers,
D. Kniep

-Oorspronkelijk bericht-
Van: Andreas Kretschmer akretsch...@spamfence.net
Verzonden: za 01-01-11 10:04:11
Aan: pgsql-general@postgresql.org; 
Onderwerp: Re: [GENERAL] Shit happens

 Dick Kniep dick.kn...@lindix.nl wrote:
 
  Hi list,
  
  Happy newyear to all of you.
  
  We use WAL files for backup purposes. Now we need to restore up to a
  certain date in december, we have a base backup from october 24th, but
  the WAL files start only at october 28th. Is there a way that we can
  use the WAL files to recover data up to half december anyway? I know
 
 No. You can't fill the gap.
 
 
 Andreas
 -- 
 Really, I'm not out to destroy Microsoft. That will just be a completely
 unintentional side effect.  (Linus Torvalds)
 If I was god, I would recompile penguin with --enable-fly.   (unknown)
 Kaufbach, Saxony, Germany, Europe.  N 51.05082°, E 13.56889°
 
 -- 
 Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
 To make changes to your subscription:
 http://www.postgresql.org/mailpref/pgsql-general
 
 !DSPAM:4d1eee0b160842018316037!
 


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

[GENERAL] Shit happens

[Dick Kniep]

Hi list,

Happy newyear to all of you.

We use WAL files for backup purposes. Now we need to restore up to a certain 
date in december, we have a base backup from october 24th, but the WAL files 
start only at october 28th. Is there a way that we can use the WAL files to 
recover data up to half december anyway? I know it is a long shot, but we would 
be VERY happy if there is a way to have a (possibly inconsistent) database that 
contains the data from the base backup plus the data from the WAL files.  

Cheers,
D.Kniep

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

[GENERAL] Waarschuwing na pg_dumpall en restore

[Dick Kniep]

Hi List,

 

Thanks for this great product.

 

We have a database with many schema's and we are trying to migrate the db from 
version 8.1.9 to 8.3.8.

 

So we made a pg_dumpall.

 

Now, when we try to restore with psql, we get loads of errors and warnings:


PROBLEM 1

 

psql:db03.ak1.sql:10155: WARNING:  column plan_status has type unknown
DETAIL:  Proceeding with relation creation anyway.
psql:db03.ak1.sql:10155: WARNING:  column plan_id has type unknown
DETAIL:  Proceeding with relation creation anyway.
psql:db03.ak1.sql:10155: WARNING:  column hplan_id has type unknown
DETAIL:  Proceeding with relation creation anyway.
psql:db03.ak1.sql:10155: WARNING:  column stp_id has type unknown
DETAIL:  Proceeding with relation creation anyway.
psql:db03.ak1.sql:10155: WARNING:  column plan_datum_start has type unknown
DETAIL:  Proceeding with relation creation anyway.
psql:db03.ak1.sql:10155: WARNING:  column plan_datum_eind has type unknown
DETAIL:  Proceeding with relation creation anyway.
psql:db03.ak1.sql:10155: WARNING:  column plan_inspanning has type unknown
DETAIL:  Proceeding with relation creation anyway.
psql:db03.ak1.sql:10155: WARNING:  column real_datum_start has type unknown
DETAIL:  Proceeding with relation creation anyway.
psql:db03.ak1.sql:10155: WARNING:  column real_datum_eind has type unknown
DETAIL:  Proceeding with relation creation anyway.
psql:db03.ak1.sql:10155: WARNING:  column med_uitvoering has type unknown
DETAIL:  Proceeding with relation creation anyway.
psql:db03.ak1.sql:10155: WARNING:  column conflict has type unknown
DETAIL:  Proceeding with relation creation anyway.
psql:db03.ak1.sql:10155: WARNING:  column conflict_text has type unknown
DETAIL:  Proceeding with relation creation anyway.
psql:db03.ak1.sql:10155: WARNING:  column handmatig_actief has type unknown
DETAIL:  Proceeding with relation creation anyway.
psql:db03.ak1.sql:10155: WARNING:  column mijlpaal has type unknown
DETAIL:  Proceeding with relation creation anyway.
psql:db03.ak1.sql:10155: WARNING:  column handmatig_groep has type unknown
DETAIL:  Proceeding with relation creation anyway.
psql:db03.ak1.sql:10155: WARNING:  column mijlpaaldatum has type unknown
DETAIL:  Proceeding with relation creation anyway.
psql:db03.ak1.sql:10155: WARNING:  column plan_pro_noshow_id has type 
unknown
DETAIL:  Proceeding with relation creation anyway.
psql:db03.ak1.sql:10155: WARNING:  column doc_id has type unknown
DETAIL:  Proceeding with relation creation anyway.
psql:db03.ak1.sql:10185: WARNING:  column plan_status has type unknown
DETAIL:  Proceeding with relation creation anyway.
psql:db03.ak1.sql:10185: WARNING:  column plan_id has type unknown
DETAIL:  Proceeding with relation creation anyway.
psql:db03.ak1.sql:10185: WARNING:  column hplan_id has type unknown
DETAIL:  Proceeding with relation creation anyway.
psql:db03.ak1.sql:10185: WARNING:  column stp_id has type unknown
DETAIL:  Proceeding with relation creation anyway.
psql:db03.ak1.sql:10185: WARNING:  column plan_datum_start has type unknown
DETAIL:  Proceeding with relation creation anyway.
 

From earlier reports on the mailinglist, I assume it is a view that has no 
explicit cast.

 

What are the consequences of these messages for the system?

 

Can I ignore the warnings and simply correct the views later?

 

PROBLEM 2

 

Furthermore the following errors are shown:

 

psql:db03.ak1.sql:251166: ERROR:  column a.transaction does not exist
LINE 2: SELECT (a.relation)::regclass AS table, a.transaction...
  ^
psql:db03.ak1.sql:251169: ERROR:  relation public.locks_tb_aux does not exist
psql:db03.ak1.sql:251176: ERROR:  relation locks_tb_aux does not exist
psql:db03.ak1.sql:251179: ERROR:  relation public.locks_tb does not exist
psql:db03.ak1.sql:251186: ERROR:  column a.transaction does not exist
LINE 2: SELECT a.transaction, a.pid AS pid_locked, b.pid AS pi...
   ^
psql:db03.ak1.sql:251189: ERROR:  relation public.locks_tr_aux does not exist
psql:db03.ak1.sql:251196: ERROR:  relation locks_tr_aux does not exist
psql:db03.ak1.sql:251199: ERROR:  relation public.locks_tr does not exist
psql:db03.ak1.sql:251206: ERROR:  column a.transaction does not exist
LINE 2: SELECT a.transaction, a.pid AS pid_locked, b.pid AS pi...
   ^
psql:db03.ak1.sql:251209: ERROR:  relation public.locks_trid_aux does not 
exist
psql:db03.ak1.sql:251216: ERROR:  relation locks_trid_aux does not exist
psql:db03.ak1.sql:251219: ERROR:  relation public.locks_trid does not exist
 

These look to me as system tables and are certainly not defined by us.

 

Can I ignore these messages?

 

PROBLEM 3

 

We get some messages that referential integrity rules (foreign keys) are 
violated. How can that be? This undermines my confidence in the system!! This 
would imply that the foreign key at one time did not exist, BUT WHEN IT WAS 
CREATED IT DID NOT COMPLAIN THAT THE RULES WERE 

[GENERAL] Warnings after pg_dumpall restore

[Dick Kniep]

Hi List,

 

Thanks for this great product.

 

We have a database with many schema's and we are trying to migrate the db from 
version 8.1.9 to 8.3.8.

 

So we made a pg_dumpall.

 

Now, when we try to restore with psql, we get loads of errors and warnings:


PROBLEM 1

 

psql:db03.ak1.sql:10155: WARNING:  column plan_status has type unknown
DETAIL:  Proceeding with relation creation anyway.
psql:db03.ak1.sql:10155: WARNING:  column plan_id has type unknown
DETAIL:  Proceeding with relation creation anyway.
psql:db03.ak1.sql:10155: WARNING:  column hplan_id has type unknown
DETAIL:  Proceeding with relation creation anyway.
psql:db03.ak1.sql:10155: WARNING:  column stp_id has type unknown
DETAIL:  Proceeding with relation creation anyway.
psql:db03.ak1.sql:10155: WARNING:  column plan_datum_start has type unknown
DETAIL:  Proceeding with relation creation anyway.
psql:db03.ak1.sql:10155: WARNING:  column plan_datum_eind has type unknown
DETAIL:  Proceeding with relation creation anyway.
psql:db03.ak1.sql:10155: WARNING:  column plan_inspanning has type unknown
DETAIL:  Proceeding with relation creation anyway.
psql:db03.ak1.sql:10155: WARNING:  column real_datum_start has type unknown
DETAIL:  Proceeding with relation creation anyway.
psql:db03.ak1.sql:10155: WARNING:  column real_datum_eind has type unknown
DETAIL:  Proceeding with relation creation anyway.
psql:db03.ak1.sql:10155: WARNING:  column med_uitvoering has type unknown
DETAIL:  Proceeding with relation creation anyway.
psql:db03.ak1.sql:10155: WARNING:  column conflict has type unknown
DETAIL:  Proceeding with relation creation anyway.
psql:db03.ak1.sql:10155: WARNING:  column conflict_text has type unknown
DETAIL:  Proceeding with relation creation anyway.
psql:db03.ak1.sql:10155: WARNING:  column handmatig_actief has type unknown
DETAIL:  Proceeding with relation creation anyway.
psql:db03.ak1.sql:10155: WARNING:  column mijlpaal has type unknown
DETAIL:  Proceeding with relation creation anyway.
psql:db03.ak1.sql:10155: WARNING:  column handmatig_groep has type unknown
DETAIL:  Proceeding with relation creation anyway.
psql:db03.ak1.sql:10155: WARNING:  column mijlpaaldatum has type unknown
DETAIL:  Proceeding with relation creation anyway.
psql:db03.ak1.sql:10155: WARNING:  column plan_pro_noshow_id has type 
unknown
DETAIL:  Proceeding with relation creation anyway.
psql:db03.ak1.sql:10155: WARNING:  column doc_id has type unknown
DETAIL:  Proceeding with relation creation anyway.
psql:db03.ak1.sql:10185: WARNING:  column plan_status has type unknown
DETAIL:  Proceeding with relation creation anyway.
psql:db03.ak1.sql:10185: WARNING:  column plan_id has type unknown
DETAIL:  Proceeding with relation creation anyway.
psql:db03.ak1.sql:10185: WARNING:  column hplan_id has type unknown
DETAIL:  Proceeding with relation creation anyway.
psql:db03.ak1.sql:10185: WARNING:  column stp_id has type unknown
DETAIL:  Proceeding with relation creation anyway.
psql:db03.ak1.sql:10185: WARNING:  column plan_datum_start has type unknown
DETAIL:  Proceeding with relation creation anyway.
 

From earlier reports on the mailinglist, I assume it is a view that has no 
explicit cast. 

 

What are the consequences of these messages for the system? 

 

Can I ignore the warnings and simply correct the views later?

 

PROBLEM 2

 

Furthermore the following errors are shown:

 

psql:db03.ak1.sql:251166: ERROR:  column a.transaction does not exist
LINE 2: SELECT (a.relation)::regclass AS table, a.transaction...
  ^
psql:db03.ak1.sql:251169: ERROR:  relation public.locks_tb_aux does not exist
psql:db03.ak1.sql:251176: ERROR:  relation locks_tb_aux does not exist
psql:db03.ak1.sql:251179: ERROR:  relation public.locks_tb does not exist
psql:db03.ak1.sql:251186: ERROR:  column a.transaction does not exist
LINE 2: SELECT a.transaction, a.pid AS pid_locked, b.pid AS pi...
   ^
psql:db03.ak1.sql:251189: ERROR:  relation public.locks_tr_aux does not exist
psql:db03.ak1.sql:251196: ERROR:  relation locks_tr_aux does not exist
psql:db03.ak1.sql:251199: ERROR:  relation public.locks_tr does not exist
psql:db03.ak1.sql:251206: ERROR:  column a.transaction does not exist
LINE 2: SELECT a.transaction, a.pid AS pid_locked, b.pid AS pi...
   ^
psql:db03.ak1.sql:251209: ERROR:  relation public.locks_trid_aux does not 
exist
psql:db03.ak1.sql:251216: ERROR:  relation locks_trid_aux does not exist
psql:db03.ak1.sql:251219: ERROR:  relation public.locks_trid does not exist
 

These look to me as system tables and are certainly not defined by us. 

 

Can I ignore these messages?

 

PROBLEM 3

 

We get some messages that referential integrity rules (foreign keys) are 
violated. How can that be? This undermines my confidence in the system!! This 
would imply that the foreign key at one time did not exist, BUT WHEN IT WAS 
CREATED IT DID NOT COMPLAIN THAT THE RULES WERE 

[GENERAL] Date range problem on pg_restore

[Dick Kniep]

Hi list,

 

We have a peculiar problem with a restore.

 

We have a database with many different schema's that all act as separate 
databases. 

 

The data is backed up from postgresql 8.1 with the following commands in cron:

 

28 12 * * * /usr/bin/pg_dump -a -F c --disable-triggers -f 
/srv/currentBackup/comsupportdata --schema=comsupport cvix

16 1 * * * /usr/bin/pg_dump -s -f /srv/currentBackup/comsupportschema 
--schema=comsupport cvix

 

As you can see the schema is backed up separately from the data.

 

When I am trying to restore the data into another 8.1 postgresql db I get the 
following errors:

 

postg...@dev01:/home/dick$ pg_restore --disable-triggers --schema=comsupport -d 
cvix_test comsupportdata
pg_restore: ERROR:  date out of range: 11750113-05-05
CONTEXT:  COPY planning, line 71654, column mijlpaaldatum: 11750113-05-05
pg_restore: [archiver (db)] error returned by PQendcopy: ERROR:  date out of 
range: 11750113-05-05
CONTEXT:  COPY planning, line 71654, column mijlpaaldatum: 11750113-05-05

 

and the restore stops.

 

I have tried to get offending rows in the source db, but if i do a select on 
the table planning with a mijlpaaldatum  '-01-01' I get 0 rows back. So it 
looks like a problem in the backup.

 

Do you have any idea what I can do to get my data back?

 

Cheers,

D.Kniep
 

[GENERAL] Userid error

[Dick Kniep]

Hi list,

We are using Linux SuSE 9.3 with psycopg 1.18 and postgresql 7.4.8.
Sometimes we get the following error message on varying queries:

2006-01-18 13:19:17,807 ERROR SQLDict 185 Fout in Select ERROR:  user with ID
322 does not exist

SELECT per_id, per_naam1, per_naam2, per_telefoonnr_thuis,
per_telefoonnr_mobiel, per_faxnr, per_mailadres_thuis, 
per_mailadres_werk, 
per_adres, per_opmerkingen, mutmed, mutdat, per_aanhef,
per_geboortedatum, per_sofinr, per_geslacht, per_aanmaakdatum,
per_debiteur_t
ime, per_debiteur_toegevoegd, per_roepnaam, 
per_adressering_meisjesnaam,
per_initialen, per_tussenvoegsels, per_meisjesnaam, per_achterna
am, cli_geboorteplaats, cli_nationaliteit, cli_burgerlijke_staat,
cli_vluchteling, cli_verblijfsvergunning, cli_gehandicapt, cli_autoallo
, cli_woonsituatie, cli_roepnaam, cli_eigen_werknr,
cli_soort_identificatie, cli_identificatie, cli_aantkinderen,
cli_rijbewijs, cli_uw
vcode, cli_cwicode, cli_opschort_vanaf, cli_opschort_tot,
cli_verzuimcoordinator, cli_opschort_actief FROM 
adeuxproductie.clienten
 sqd1
  WHERE per_id in ((select cli_id from adeuxproductie.betrokken_opdrachten
where betrokkene = 1061  and (opd_status  90 OR (opd_status = 90 AND ((o
pd_real_einde is NULL or opd_real_einde  '2006-01-04') OR uit_categorie is 
NULL
                        UNION (select cli_id from adeuxproductie.planning 
et1,
adeuxproductie.activiteiten_stappen et2, adeuxproductie.opdrachten
 et3,  adeuxproductie.groepen_per_medewerker et4
                        where et1.opd_id = et3.opd_id and et1.stp_id =
et2.tab_id and et4.mwg_id = et2.act_med_groep and med_id = 1061  and 
(opd_status
  90 OR (opd_status = 90 AND ((opd_real_einde is NULL or opd_real_einde 
'2006-01-04') OR uit_categorie is NULL)

It looks as if the user that is connected to the database suddenly doesn't 
exist anymore. The user has been able to connect, to work for a while, and 
after a while this error occurs.

Anyone any ideas? Could this be a problem in libpq, or 
postgresql?

Thanks in advance
Dick Kniep

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly

[GENERAL] Schema's versus tablespace

[Dick Kniep]

Hi list,

We have developed a system that is running as an ASP application, currently we 
are using Postgresql 7.4. For every customer we have a separate schema, and 
this works OK. Now in version 8, tablespaces were introduced. This summer we 
are planning to migrate to version 8.1. Question is if there are advantages 
to use tablespaces instead of schema's?

Keep up the good work

Cheers,
Dick Kniep

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

Re: [GENERAL] Schema's versus tablespace

[Dick Kniep]

OK, that's cleared, thanks,

Now another question related to this. In 7.4.8 I have a schema with the 
following authorization:

CREATE SCHEMA adeuxproductie
  AUTHORIZATION postgres;
GRANT ALL ON SCHEMA adeuxproductie TO postgres;
GRANT ALL ON SCHEMA adeuxproductie TO GROUP adeuxproductie;
COMMENT ON SCHEMA adeuxproductie IS 'Standard production schema';

Now when I try to access the tables/views in the schema as a user that is NOT 
a member of the group adeuxproductie, I expect it to fail. However, access is 
granted as if there is no authorization specified.

Is this a bug, or do I misunderstand something?

Cheers,
Dick

On Tuesday 28 March 2006 09:48, Martijn van Oosterhout wrote:
 On Tue, Mar 28, 2006 at 09:41:46AM +0200, Dick Kniep wrote:
  Hi list,
 
  We have developed a system that is running as an ASP application,
  currently we are using Postgresql 7.4. For every customer we have a
  separate schema, and this works OK. Now in version 8, tablespaces were
  introduced. This summer we are planning to migrate to version 8.1.
  Question is if there are advantages to use tablespaces instead of
  schema's?

 Schemas related to how your tables are logically divided up.
 Tablespaces are about where the tables are actually stored on disk. As
 such they have nothing to do with eachother. You can mix and match as
 you please.

 Have a nice day,

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [GENERAL] B-tree performance improvements in 8.x

[Dick Kniep]

On Wednesday 08 February 2006 06:18, Tom Lane wrote:
 Dick Kniep [EMAIL PROTECTED] writes:
  Does this also affect if you have many NULL values in the key? So testing
  Not is NULL would also be affected?

 IS NOT NULL isn't an indexable operation, so your question doesn't really
 apply :-(

Does this mean that if you have a table that has many rows, and 95% of the 
rows contain a NULL value for a field, that indexing will be useless, because 
it will always do a tablescan?

Hope not!

Dick

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [GENERAL] B-tree performance improvements in 8.x

[Dick Kniep]

Hi list,

Does this also affect if you have many NULL values in the key? So testing Not 
is NULL would also be affected?

Cheers,
Dick Kniep

On Tuesday 07 February 2006 23:13, Tom Lane wrote:
 [EMAIL PROTECTED] writes:
  Quoting Tom Lane [EMAIL PROTECTED]:
  In 8.0, the descent code can do
  either first entry = X or first entry  X, and the positioning
  rules never need to step more than one entry to locate the desired
  starting position (details left as exercise for the reader).
 
  What about a compound index (x, y), in which there are many y values
  for a given x? My query is ... WHERE x = ? and y = ?.

 Doesn't really matter whether the key is simple or compound --- all
 that matters is whether you have multiple entries that are equal to
 the boundary value.

   regards, tom lane

 ---(end of broadcast)---
 TIP 6: explain analyze is your friend

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [GENERAL] Connected user in a triggerfunction

[Dick Kniep]

Anyone?

Op vrijdag 20 januari 2006 07:56, schreef Dick Kniep:
 Hi list,

 We are using logging in a database based on triggers and plpgsql functions.
 This works OK. However, we want deletes to be recorded too, and there we
 want the user who connected to be recorded in the log. So, how can I get
 the connected user in a triggerfunction?

 Cheers,
 Dick Kniep

 ---(end of broadcast)---
 TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

Re: [GENERAL] Connected user in a triggerfunction

[Dick Kniep]

Oops, Sorry, didn't get the answers untill just now...

Op donderdag 26 januari 2006 10:02, schreef Richard Huxton:
 Dick Kniep wrote:
  Anyone?

 I thought I saw an answer to this yesterday. Have you tried CURRENT_USER
 ? It's in the functions and operators section of the manuals (contrary
 to appearance, it is a function).

  Op vrijdag 20 januari 2006 07:56, schreef Dick Kniep:
  Hi list,
 
  We are using logging in a database based on triggers and plpgsql
  functions. This works OK. However, we want deletes to be recorded too,
  and there we want the user who connected to be recorded in the log. So,
  how can I get the connected user in a triggerfunction?
 
  Cheers,
  Dick Kniep

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

[GENERAL] Connected user in a triggerfunction

[Dick Kniep]

Hi list,

We are using logging in a database based on triggers and plpgsql functions. 
This works OK. However, we want deletes to be recorded too, and there we want 
the user who connected to be recorded in the log. So, how can I get the 
connected user in a triggerfunction?

Cheers,
Dick Kniep

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [GENERAL] A good client

[Dick Kniep]

Op dinsdag 18 oktober 2005 16:30, schreef Raymond O'Donnell:
 On 18 Oct 2005 at 15:41, Wim Bertels wrote:
  When i look at pgadmin: an anoying feature: it closes when u do
  something illegal, instead of saying: u don't have the rights to do
  this.

 You must be using an old version - pgAmin used to do that at one
 time, and indeed it was VERY annoying, but that was ages ago. The
 current version is pgAdmin III 1.2.2, and a newer version is in beta.

I second that, although i do get an occasional crash, it is VERY usable.

Just my nickel ;-)

D.Kniep

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org

[GENERAL] Cannot compile on Slackware 10.2

[Dick Kniep]

Hi list,

I downloaded Postgresql 8.0.4, and tried to compile on Slackware 10.2. 
However, the configure runs fine, but when I run 'make' (or 'gmake') after 
that, it executes the configure again (and again, and again...), without 
actually making the application.

Any ideas?

Cheers,
D.Kniep

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

[GENERAL] weird problem with grants

[Dick Kniep]

Hi list,

I have a weird problem with grants. Probably I am forgetting something, but I 
simply don't understand it.

We have a user 'x' that is member of group 'a'
there is a sequence where 
Grant all on table schema.sequence to group 'a'

But still I get a permission denied when I try to access the sequence as user 
'x'.

Thanks in advance.

D.Kniep

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly

Re: [GENERAL] Schema problems RedHat / SuSE 9.3 for version 7.4.8

[Dick Kniep]

Hi list,

Again after reconsidering the way this problem came to be, it struck me that I  
used a pg_dump -f /tmp/dumpdb and restored with psql. How can it be that not 
all grants of the schema's, views and tables are restored? There were no 
messages during the restore that could be interpreted as an indication that 
the grants were not restored.

Op vrijdag 2 september 2005 14:35, schreef Dick Kniep:
 Hi list/Michael,

 Sorry I forgot reply to all

 It proved to be a problem with the permissions on the table and view! So,
 the error that was reported was completely different from the actual error.
 I do not know how this can happen, but by making a direct connection to the
 database within Zope, I was able to get the real error message.

 I will investigate further how the reporting of the messages got confused.
 It could be a problem in Zope or in psycopg. If I find something
 interesting I will report back to the list.

 Thanks for the help.

 Dick

 Op vrijdag 2 september 2005 00:04, schreef Michael Fuhr:
  [Please copy the mailing list on replies so others can contribute
  to and learn from the discussion.  I've quoted more of your message
  than I ordinarily would because other people won't have seen it and
  they won't find it in the list archives.]
 
  On Thu, Sep 01, 2005 at 11:35:43PM +0200, Dick Kniep wrote:
   After starting psql, and executing the query, without a begin, after
   the query there is no search path
  
   SELECT set_config('search_path', '' || t2.schema || '', true) FROM
   Lindix.Gebruikers as t1, Lindix.Administratie as t2 WHERE uid =
   'zon0023' AND t1.administratie_id = t2.administratie_id;
   set_config
   --
adeuxproductie
   (1 row)
  
   cvix=# SHOW search_path;
search_path
   --
$user,public
   (1 row)
 
  Apparently you're in autocommit mode, which is the default for psql.
  Each statement is its own transaction, so you won't see the effects
  of set_config() when the third argument is true.
 
   Executed with third parameter false:
  
   cvix=# SELECT set_config('search_path', '' || t2.schema || '', false)
   FROM Lindix.Gebruikers as t1, Lindix.Administratie as t2 WHERE
   uid = 'zon0023' AND t1.administratie_id = t2.administratie_id;
   set_config
   --
adeuxproductie
   (1 row)
  
   cvix=# SHOW search_path;
  search_path
   --
adeuxproductie
   (1 row)
  
   Also the same result when I have a begin before the first statement.
   Which means that it seems to work correctly!
 
  Yep.  If you're in a transaction block, or if you tell set_config()
  not to make the change local to the transaction, then you see the
  new setting take effect.
 
   Also, a thing I hadn't checked before, is that the psql results on the
   2 servers are the same. Which leads to my conclusion that the
   autocommit settings are indeed different on the 2 servers.
 
  What do SELECT version() and SHOW autocommit show on both
  servers?  If both servers are running 7.4 then they can't have
  different autocommit settings because 7.4 and later don't support
  server-side autocommit (it always shows on and you can't change
  it).  Unless one of the servers is running 7.3, the autocommit
  settings must be on the client side.  Are you using the same instance
  of the client to connect to both servers?
 
   OK, next question, how do I get rid of the autocommit in my
   application? I tried set autocommit to off; but that is deprecated.
 
  Using SET autocommit attempts to change the server-side setting,
  which was only supported in 7.3 (the developers removed it after
  deciding it had been a bad idea).  How to disable autocommit on the
  client side depends on your client interface.  What language and
  API are you using?

 ---(end of broadcast)---
 TIP 2: Don't 'kill -9' the postmaster

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly

Re: [GENERAL] Schema problems RedHat / SuSE 9.3 for version 7.4.8

[Dick Kniep]

Hi list/Michael,

Sorry I forgot reply to all

It proved to be a problem with the permissions on the table and view! So, the 
error that was reported was completely different from the actual error. I do 
not know how this can happen, but by making a direct connection to the 
database within Zope, I was able to get the real error message.

I will investigate further how the reporting of the messages got confused. It 
could be a problem in Zope or in psycopg. If I find something interesting I 
will report back to the list.

Thanks for the help.

Dick

Op vrijdag 2 september 2005 00:04, schreef Michael Fuhr:
 [Please copy the mailing list on replies so others can contribute
 to and learn from the discussion.  I've quoted more of your message
 than I ordinarily would because other people won't have seen it and
 they won't find it in the list archives.]

 On Thu, Sep 01, 2005 at 11:35:43PM +0200, Dick Kniep wrote:
  After starting psql, and executing the query, without a begin, after the
  query there is no search path
 
  SELECT set_config('search_path', '' || t2.schema || '', true) FROM
  Lindix.Gebruikers as t1, Lindix.Administratie as t2 WHERE uid =
  'zon0023' AND t1.administratie_id = t2.administratie_id;
  set_config
  --
   adeuxproductie
  (1 row)
 
  cvix=# SHOW search_path;
   search_path
  --
   $user,public
  (1 row)

 Apparently you're in autocommit mode, which is the default for psql.
 Each statement is its own transaction, so you won't see the effects
 of set_config() when the third argument is true.

  Executed with third parameter false:
 
  cvix=# SELECT set_config('search_path', '' || t2.schema || '', false)
  FROM Lindix.Gebruikers as t1, Lindix.Administratie as t2 WHERE
  uid = 'zon0023' AND t1.administratie_id = t2.administratie_id;
  set_config
  --
   adeuxproductie
  (1 row)
 
  cvix=# SHOW search_path;
 search_path
  --
   adeuxproductie
  (1 row)
 
  Also the same result when I have a begin before the first statement.
  Which means that it seems to work correctly!

 Yep.  If you're in a transaction block, or if you tell set_config()
 not to make the change local to the transaction, then you see the
 new setting take effect.

  Also, a thing I hadn't checked before, is that the psql results on the 2
  servers are the same. Which leads to my conclusion that the autocommit
  settings are indeed different on the 2 servers.

 What do SELECT version() and SHOW autocommit show on both
 servers?  If both servers are running 7.4 then they can't have
 different autocommit settings because 7.4 and later don't support
 server-side autocommit (it always shows on and you can't change
 it).  Unless one of the servers is running 7.3, the autocommit
 settings must be on the client side.  Are you using the same instance
 of the client to connect to both servers?

  OK, next question, how do I get rid of the autocommit in my application?
  I tried set autocommit to off; but that is deprecated.

 Using SET autocommit attempts to change the server-side setting,
 which was only supported in 7.3 (the developers removed it after
 deciding it had been a bad idea).  How to disable autocommit on the
 client side depends on your client interface.  What language and
 API are you using?

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

[GENERAL] Schema problems RedHat / SuSE 9.3 for version 7.4.8

[Dick Kniep]

Hi list,

we are using schema's to separate different administrations, and are using 
Zope/Plone as a front-end portal to the database. 

In this installation almost the first statement is:

cvix=# SELECT set_config('search_path', '' || t2.schema || '', true)
cvix-# FROM Lindix.Gebruikers as t1, Lindix.Administratie as t2
cvix-# WHERE uid = 'zon0023'
cvix-# AND t1.administratie_id = t2.administratie_id;
set_config
--
 testschema
(1 row)

As you can see, I have a fixed schema called Lindix where the actual 
destination schema is in a table. Depending on the user, the search_path is 
set and it should be able to find the table.

Now we have installed a new server, with the same db version, the same content 
(a restore from the original db) and the same coding.

After setting the search path the query 

Select * from vwexternetoegang 

produces the required results in the first installation, but in the new 
installation, it cannot find the view. However, if I do an explicit 

Set search_path to testschema; 

it works as expected.

I can reproduce the result not only from my own code, but also from psql.

The only real difference between the 2 installations I see is that the working 
installation has a RedHat Enterprise Linux ES Release 4 (Nahant update1) 
version versus the new (not working) a SuSE 9.3 installation.

For both installations I have compiled Postgresql from the standard source as 
is delivered from www.postgresql.org (or one of the mirrors). Difference is 
that the first installation dates back to june 27th and the new installation 
from august 20th. 

Other difference is that in the new situation, the postgres database is on the 
same machine as the Zope application is.

Hope someone has a solution for this, because the new machine should be 
launched monday.

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

[GENERAL] pg_restore and schema's

[Dick Kniep]

Hi list,

We have developed an application that makes extensive use of schema's in the 
database. Some of the schemanames are uppercase, some are lowercase.

I make a backup using 

pg_dump -f /tmp/dumpdb -F c -Z 5 cvix

When I try to restore this file, it fails with

[EMAIL PROTECTED]:/tmp pg_restore -O -d cvix2 /tmp/dumpdb
pg_restore: [archiver (db)] could not execute query: ERROR:  schema Lindix 
does not exist

What am I doing wrong, or is it a bug. After the restore the database does 
contain the schema's but all in lowercase.

Hope someone knows what to do!

Cheers,
Dick Kniep

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly

Re: [GENERAL] pg_restore and schema's

[Dick Kniep]

Hi Michael,

Yes indeed, it is 7.4.8, and I will file a bugreport.

Op maandag 22 augustus 2005 15:21, schreef Michael Fuhr:
 On Mon, Aug 22, 2005 at 09:30:36AM +0200, Dick Kniep wrote:
  [EMAIL PROTECTED]:/tmp pg_restore -O -d cvix2 /tmp/dumpdb
  pg_restore: [archiver (db)] could not execute query: ERROR:  schema
  Lindix does not exist

 What version of PostgreSQL are you using?  I can duplicate this
 problem with 7.4.8, but only with pg_restore's -O option.  If
 I turn on statement logging, I see the following when running
 pg_restore -O:

 CREATE SCHEMA Test;

 If I run pg_restore without -O, I see the following:

 CREATE SCHEMA Test AUTHORIZATION postgres;

 Since identifiers must be quoted to preserve case, I'd say the -O
 behavior is a bug.  You might wish to report this to pgsql-bugs.

 I tested 8.0.3 and it doesn't have this problem.

---(end of broadcast)---
TIP 6: explain analyze is your friend