Re: [GENERAL] xml queries date format
On Thu, 11 Sep 2008, Peter Eisentraut wrote: Tom Lane wrote: Jef Peeraer [EMAIL PROTECTED] writes: i am using the xml add-ons, but the date output format seems to be wrong : I think the conversion to xml intentionally always uses ISO date format, because that's required by some spec somewhere. Yes, it follows XML Schema. Which is why the output format is even slightly different from the SQL-mandated ISO format. i understand, but that makes it very difficult to change the date format afterwards. i simple flag to indicate no date conversion would be helpfull -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- 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] xml queries date format
On Thu, 11 Sep 2008, Peter Eisentraut wrote: Jef Peeraer wrote: On Thu, 11 Sep 2008, Peter Eisentraut wrote: Tom Lane wrote: Jef Peeraer [EMAIL PROTECTED] writes: i am using the xml add-ons, but the date output format seems to be wrong : I think the conversion to xml intentionally always uses ISO date format, because that's required by some spec somewhere. Yes, it follows XML Schema. Which is why the output format is even slightly different from the SQL-mandated ISO format. i understand, but that makes it very difficult to change the date format afterwards. i simple flag to indicate no date conversion would be helpfull Well, these table_to_xml etc. functions are heavily constrained by the SQL standard, XML Schema, and others. They do what they are supposed to do. You are free to design your own XML export format or apply postprocessing to the existing ones (XSLT?). I don't think we should overload the existing functions with everyone's favorite but apparently completely nonstandard formatting variant flag. it would be a flag to indicate no conversion from the datestyle settings in the database...i think, from a users perspective, the table_to_xml is completely useless, if you have to reformat everything afterwards jef -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] xml queries date format
i am using the xml add-ons, but the date output format seems to be wrong :
i have
show datestyle;
DateStyle
---
SQL, DMY
select agenda_datum from dossiers where id = 61;
agenda_datum
--
29/07/2008
select table_to_xml('dossiers', false, false, '');
gives (knip )
row
id62/id
voorwerp_detail5 coils 5.622 kg/voorwerp_detail
schade_datum2008-07-29/schade_datum
voorbehoudfalse/voorbehoud
protestfalse/protest
vorderingfalse/vordering
afgewezenfalse/afgewezen
gedeeltelijk_afgewezenfalse/gedeeltelijk_afgewezen
verhaalfalse/verhaal
administratieffalse/administratief
/row
jef
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] replication only
i read about the replication possibilities with postgresql. If i just need some replication ( without failover stuff ) to 1 standby server, what would be the best option to go with. Slony i presume, although schema chanages are not propagated. thanks jef peeraer -- 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] replication only
On Tue, 5 Aug 2008, Bill Moran wrote: In response to Jef Peeraer [EMAIL PROTECTED]: i read about the replication possibilities with postgresql. If i just need some replication ( without failover stuff ) to 1 standby server, what would be the best option to go with. Your description of you requirements is very lacking, so much so that any attempt at suggesting a best option would be pointless. Provide some more information on your requirements and people will be able to answer intelligently. my apology, i tried to be brief. i've got a main database, where all transactions end up. Daily we make a backup (pg_dumpall) and restore the first database in a sort of backup database. This backup database is mainly used to do reporting. To automate this process for the backup database and to minimize the out-of-sync state, i should like to use replication. The second backup database is only used as read-only, except for some third party software that writes to 1 table. jef peeraer -- Bill Moran Collaborative Fusion Inc. http://people.collaborativefusion.com/~wmoran/ [EMAIL PROTECTED] Phone: 412-422-3463x4023 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- 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] backup of postgres scheduled with cron
On Thu, 22 Nov 2007, Sorin N. Ciolofan wrote: Hello all! I've a small bash script backup.sh for creating dumps on my Postgre db: #!/bin/bash time=`date '+%d'-'%m'-'%y'` cd /home/swkm/services/test pg_dump mydb mydb_dump_$time.out I've edited crontab and added a line: 00 4 * * * swkm /home/swkm/services/test/backup.sh to execute the backup.sh as user swkm daily at 4 am. The user swkm is the user I use to create backups manually. The script itself is executed fine if run manually but run on cron scheduler I got an mydb_dump_$time.out file empty (of 0 kb) Do you have any idea about what's wrong? you have to pass the -U argument to pg_dump, otherwise it'll try to run the script as root jef Thanks Sorin ---(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 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] upgrade 8.1.4 - latest, sort order subquery
i decide this weekend to upgrade to the latest stable version from an 8.1.4 . Upgrade went smootly, as usual, but today, i've got some phonecalls of something weird. The query is as follows : registratie=# select * from module_info where type_module_id = 1; naam | korte_beschrijving | kolom_naam | xul_opties| type_veld_id | lengte | is_verplicht | type_module_id ++---+-+--++--+ alg_02 | naam | naam | | 3 | 0 | f| 1 alg_03 | voornaam | voornamen | | 3 | 0 | f| 1 alg_01 | anoniem| anoniem | | 2 | 0 | f| 1 cl_01 | geslacht | geslacht | | 1 | 0 | t| 1 cl_19 | geboortedatum | geboortedatum | hide_clock,true | 7 | 0 | f| 1 cl_02 | leeftijd | leeftijd | | 1 | 0 | t| 1 cl_15 | origine| origine | | 1 | 0 | f| 1 (7 rows) i combine this with the next query in a subquery registratie=# select * from get_parent_type_modules(1); NOTICE: first query get_parent_type_modules - 1 (1 row) The resulting query , which should return the same result as the first one registratie=# select * from module_info where type_module_id in (select * from get_parent_type_modules(1)); NOTICE: first query naam | korte_beschrijving | kolom_naam | xul_opties| type_veld_id | lengte | is_verplicht | type_module_id ++---+-+--++--+ cl_15 | origine| origine | | 1 | 0 | f| 1 cl_02 | leeftijd | leeftijd | | 1 | 0 | t| 1 cl_19 | geboortedatum | geboortedatum | hide_clock,true | 7 | 0 | f| 1 cl_01 | geslacht | geslacht | | 1 | 0 | t| 1 alg_01 | anoniem| anoniem | | 2 | 0 | f| 1 alg_03 | voornaam | voornamen | | 3 | 0 | f| 1 alg_02 | naam | naam | | 3 | 0 | f| 1 (7 rows) The order is completely ignored, although there is an order by in the view 'module_info' jef peeraer ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] permission denied for schema
Tom Lane schreef:
jef peeraer [EMAIL PROTECTED] writes:
Tom Lane schreef:
That's a foreign-key check, which is supposed to be done as the owner of
the table. You did not show us who owns table clienten, but I think
that role must be missing the intended(?) membership in deterp_group.
registratie=# \z deterp.clienten;
Access privileges for database registratie
Schema | Name | Type |Access privileges
+--+---+--
deterp | clienten | table | {jpe=arwdxt/jpe,deterp_group=arwdxt/jpe}
Hmm. This doesn't actually say so, but I think that jpe must be the
owner of clienten, and he's also the owner of the schema. Which makes
the failure really darn odd.
I tried to reproduce the situation on 8.1.9 and couldn't see any
problem. I do not see anything in the 8.1 CVS history that looks
related, but it's slightly possible this is a bug that's fixed between
8.1.3 and 8.1.9 (and even if that is not the case, you really really
ought to update anyway).
ok, i upgraded anywway to the latest stable one, also because i needed
the 'drop table if exists' feature.
it was indeed a foreign referenced table that was not owned by jpe. i
must have overlooked it.
thanks for you're time anyway.
jef peeraer
Would you see if the problem recurs if you do a pg_dump -s and load the
schema into a fresh database? If it doesn't, perhaps there is some
weird corruption in the ACL entries. If it is reproducible, please post
the complete schema (or enough of it to reproduce the problem) to
pgsql-bugs.
regards, tom lane
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
[GENERAL] permission denied for schema
i get this when i try to insert a record into a table. all permissions /
privileges seems ok, but probably i missed something. i am running a
8.1.3 on linux.
registratie= INSERT INTO clienten
(anoniem,geslacht,leeftijd,origine,team_id,hulpverlener_id) VALUES
('true','45','58','864','30','221');
ERROR: permission denied for schema deterp
CONTEXT: SQL statement SELECT 1 FROM ONLY deterp.teams x WHERE
id = $1 FOR SHARE OF x
registratie= \dn
List of schemas
Name| Owner
+---
dekempen | jpe
deterp | jpe
information_schema | postgres
mozaiek| mozaiek_admin
pg_catalog | postgres
pg_toast | postgres
public | postgres
testsaw| testsaw_admin
(8 rows)
registratie= \dn+
List of schemas
Name| Owner |
Access privileges
| Description
+---+--+--
dekempen | jpe | {jpe=UC/jpe,cawdekempen=UC/jpe}
|
deterp | jpe |
{jpe=UC/jpe,deterp_group=UC/jpe,terp_user=UC/jpe}
|
information_schema | postgres |
{postgres=UC/postgres,=U/postgres}
|
mozaiek| mozaiek_admin |
{mozaiek_admin=UC/mozaiek_admin,jpe=UC/mozaiek_admin,mozaiek_user=UC/mozaiek_admin,mozaiek=UC/mozaiek_admin}
|
pg_catalog | postgres |
{postgres=UC/postgres,=U/postgres}
| System catalog schema
pg_toast | postgres |
| Reserved schema for TOAST tables
public | postgres |
{postgres=UC/postgres,=UC/postgres,deterp_group=U/postgres,cawdekempen=U/postgres}
| Standard public schema
testsaw| testsaw_admin |
{testsaw_admin=UC/testsaw_admin,jpe=UC/testsaw_admin,testsaw_user=UC/testsaw_admin,testsaw=UC/testsaw_admin}
|
(8 rows)
registratie= \d clienten
Table deterp.clienten
Column | Type | Modifiers
-+--+---
id | integer | not null default
nextval('clienten_id_seq'::regclass)
anoniem | boolean | not null default false
naam| text |
voornamen | text |
geslacht| smallint |
leeftijd| smallint |
geboortedatum | date |
origine | smallint |
team_id | integer |
hulpverlener_id | integer |
Indexes:
pk_deterp_clienten PRIMARY KEY, btree (id)
Foreign-key constraints:
fk_clienten_geslacht FOREIGN KEY (geslacht) REFERENCES
lijst_items(score) ON UPDATE RESTRICT ON DELETE RESTRICT
fk_clienten_hv_id FOREIGN KEY (hulpverlener_id) REFERENCES
gebruikers(id) ON UPDATE RESTRICT ON DELETE RESTRICT
fk_clienten_leeftijd FOREIGN KEY (leeftijd) REFERENCES
lijst_items(score) ON UPDATE RESTRICT ON DELETE RESTRICT
fk_deterp_clienten_team_id FOREIGN KEY (team_id) REFERENCES
teams(id) ON UPDATE RESTRICT ON DELETE RESTRICT
registratie= \z clienten_id_seq
Access privileges for database registratie
Schema | Name | Type | Access privileges
+-+--+
deterp | clienten_id_seq | sequence |
{jpe=arwdRxt/jpe,deterp_group=arwdRxt/jpe}
(1 row)
registratie= \dg
List of roles
Role name| Superuser | Create role | Create DB | Connections |
Member of
+---+-+---+-+
cawdekempen| no| no | no| no limit|
db_admin | yes | yes | yes | no limit|
dekempen_admin | no| no | no| no limit|
{cawdekempen}
dekempen_user | no| no | no| no limit|
{cawdekempen}
deterp | no| no | no| no limit|
deterp_group | no| no | no| no limit|
deterp_resp| no| no | no| no limit|
jpe| yes | yes | yes | no limit|
{mozaiek}
liesbet| no| no | no| no limit|
{cawdekempen}
mozaiek| no| no | no| no limit|
mozaiek_admin | no| yes | yes | no limit|
{mozaiek}
mozaiek_user | no| no | no| no limit|
{mozaiek}
postgres | yes | yes | yes | no limit|
terp_admin | no| no | no| no limit|
{deterp_group}
terp_user | no|
Re: [GENERAL] permission denied for schema
Tom Lane schreef:
jef peeraer [EMAIL PROTECTED] writes:
i get this when i try to insert a record into a table. all permissions /
privileges seems ok, but probably i missed something. i am running a
8.1.3 on linux.
registratie= INSERT INTO clienten
(anoniem,geslacht,leeftijd,origine,team_id,hulpverlener_id) VALUES
('true','45','58','864','30','221');
ERROR: permission denied for schema deterp
CONTEXT: SQL statement SELECT 1 FROM ONLY deterp.teams x WHERE
id = $1 FOR SHARE OF x
That's a foreign-key check, which is supposed to be done as the owner of
the table. You did not show us who owns table clienten, but I think
that role must be missing the intended(?) membership in deterp_group.
registratie=# \z deterp.clienten;
Access privileges for database registratie
Schema | Name | Type |Access privileges
+--+---+--
deterp | clienten | table | {jpe=arwdxt/jpe,deterp_group=arwdxt/jpe}
(1 row)
regards, tom lane
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
[GENERAL] copy template X - Y
is it possible to copy one template, say X to a new template , called Y ? Or better, i want a sort of a basic template that can be used to create a new schema. jef ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] encoding problem
jef peeraer schreef: i never thought i would be bblocked by an encoding problem :-( My database is in LATIN1 , i have entries like this in a table called gemeenten Column | Type | Modifiers ---+--+ id| integer | serial gemeente | text | not null postcode | smallint | not null provincies_id | integer | This data is copied from a dump from that table 9780Quévy70407 9781Quévy-le-Grand70407 9782Quévy-le-Petit70407 So, the accents are there. But with my web page, which is set to ISO-8859-1, i don't get the accents. The web-pages are build with XUL, where i set the charset to ISO-8859-1, but communication with the server is through XMLHttpRequest. Do I have to specify the charset as well in the communication between server and client ? Or where else could it go wrong. jef peeraer ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster thanks for the help. The problem was lying in the fact that use json_encode procedure from PHP. This procedure requires that the string to be encoded be in UTF-8 format. So basically, my database stays in LATIN1 and i set client encoding to UTF-8, as well as all web pages. jef peeraer ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] encoding problem
i never thought i would be bblocked by an encoding problem :-( My database is in LATIN1 , i have entries like this in a table called gemeenten Column | Type | Modifiers ---+--+ id| integer | serial gemeente | text | not null postcode | smallint | not null provincies_id | integer | This data is copied from a dump from that table 9780Quévy 70407 9781Quévy-le-Grand 70407 9782Quévy-le-Petit 70407 So, the accents are there. But with my web page, which is set to ISO-8859-1, i don't get the accents. The web-pages are build with XUL, where i set the charset to ISO-8859-1, but communication with the server is through XMLHttpRequest. Do I have to specify the charset as well in the communication between server and client ? Or where else could it go wrong. jef peeraer ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] pl/pgsql NEW variable substitution
i want to check in a trigger if certain columns are not left empty. The columns i have to check are stored in another table. How do i do the following BEGIN SELECT INTO col_record * FROM modules WHERE type_module_id = NEW.type_module_id AND is_afsluit_kolom; IF NOT FOUND THEN RAISE EXCEPTION 'geen afsluitkolom gedefinieerd voor type_module % ', NEW.type_module_id; ELSE col_naam := col_record.kolom_naam; RAISE NOTICE 'kolom to check is % ', col_naam; --- check if afsluitrecord is empty IF NEW.col_naam != '' THEN RETURN NEW; ELSE RAISE EXCEPTION 'dit record is afgesloten voor wijzigingen !'; END IF; END IF; END; I want col_naam to be evaluated before used with NEW . Now i get a warning that the table the trigger is written for doesn't has a column 'col_naam'. jef peeraer ---(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] Encoding Conversion
beer schreef: Hello All So I have an old database that is ASCII_SQL encoded. For a variety of reasons I need to convert the database to UNICODE. I did some googling on this but have yet to find anything that looked like a viable option, so i thought I'd post to the group and see what sort of advice might arise. :) well i recently struggled with the same problem. After a lot of trial and error and reading, it seems that an ascii encoded database can't use its client encoding capabilities ( set client_encoding to utf8 ). i think the easist solution is to do a dump, recreate the database with a proper encoding, and restore the dump. jef peeraer TIA -b ---(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 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Encoding Conversion
beer schreef: Hello All So I have an old database that is ASCII_SQL encoded. For a variety of reasons I need to convert the database to UNICODE. I did some googling on this but have yet to find anything that looked like a viable option, so i thought I'd post to the group and see what sort of advice might arise. :) well i recently struggled with the same problem. After a lot of trial and error and reading, it seems that an ascii encoded database can't use its client encoding capabilities ( set client_encoding to utf8 ). i think the easist solution is to do a dump, recreate the database with a proper encoding, and restore the dump. jef peeraer TIA -b ---(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 ---(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] inheritance/foreignkeys
i have a project where inheritance could be used, i think. but after going through the lists and having read the FM's, i realize that i beter use another approach. i just describe the project in short : users have to fill in forms, that mainly exists of different lists and/or textboxes. Users are part of teams ( kind of groups) , and every team is allowed to have its extra listitems, which should be added to the global lists ( as i said, this could be solved with inheritance...). The teams should be able to insert their extra listitems in a table, without touching the global listitems. This could be solved by using a different schema for each team, and each team has its own listitems table. The selected items will end up in a registration table, also specific per team. public.listitems id serial id_up int priority int2 label text teamX.listitems like public.listitems teamY.listitems like public.listitems teamX.registration reg_id serial time_registration date list_id foreign i can create the composed listitems for a team by using a select and UNION of the two tables. I am a little bit stuck how i should implement the foreign key in the registration table as the list_id can reference a listitem of the public schema as well of the teams schema. 1-maybe i should use triggers, and all listiteems inserts by the teams should be inserted in the public.listitems as well 2- create a new table that consists of union of both tables 3- other solution jef peeraer ---(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
