[GENERAL] pg_toast record in table pg_class
Hi. I accidentally deleted pg_toast record from pg_class table that belongs to regular table (reltoastrelid). Now [select * from table_name] not work: ERROR: could not open relation with OID [oid_of_pg_toast_table] Is it possible to restore corruption table? -- --- Regards, M.Nasedkin -- 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] Looking for way to replicate master db to multiple mobile databases
Thanks for the reply. This is a one way push to the slaves. In theory, there shouldn't be any conflicts although I wouldn't swear to that. If there's a conflict, the master db should win. At the moment we just drop the tables, recreate the schema and reload the tables. However, some of the large tables literally take hours across the network, for maybe a few dozen changes. On Wed, Sep 23, 2009 at 4:58 PM, Scott Marlowe wrote: > On Wed, Sep 23, 2009 at 11:11 AM, Bryan Montgomery > wrote: > > Hi, > > I'm looking for a way to replicate am master database to multiple (100+) > > databases that are taken in to the field. Currently for each laptop we > dump > > and load the tables. However,there is only a small percentage of data > that > > changes on a frequent basis. > > > > I've been looking around and come across pyerplica, londiste and bucardo > - > > the documentation on most of these is fairly sparse. It seems that > Bucardo > > may be the best bet - at least initially. > > > > However, I thought I'd see if anyone is doing something similar and what > > thoughts there might be out there as to a better way to accomplish this. > > The problem domain you're working on is a bit different from regular > replication. Most replication solutions are made to keep two machines > that talk to each other all the time in sync. Disconnect one machine > and maybe replication will resume properly, and maybe it wont. > > So, do you need the slave databases to be updatable? Do you need the > changes to go back into the master? Do you need conflict resolution? > Depending on the full fleshed out requirements, you may be stuck > writing your own solution, or re-writing one somebody already wrote. >
Re: [GENERAL] Looking for way to replicate master db to multiple mobile databases
On Wed, Sep 23, 2009 at 06:00:03PM -0400, Bryan Montgomery wrote: > Thanks for the reply. This is a one way push to the slaves. In theory, there > shouldn't be any conflicts although I wouldn't swear to that. If > there's a conflict, the master db should win. At the moment we just drop the > tables, recreate the schema and reload the tables. However, some of the > large tables literally take hours across the network, for maybe a few dozen > changes. Could you just replay WAL updates? I.e. have two copies of the database on each device, one as the mirror of the "master" and one as the "live" version. When you need to push the changes out, just push out the WAL updates, dump the "live" version, copy the "master" into a new "live" version and then replay the new WAL records. Depends on how much disk space you have I guess. -- Sam http://samason.me.uk/ -- 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] Looking for way to replicate master db to multiple mobile databases
Hi, On Wed, Sep 23, 2009 at 8:11 PM, Bryan Montgomery wrote: > Hi, > I'm looking for a way to replicate am master database to multiple (100+) > databases that are taken in to the field. Currently for each laptop we dump > and load the tables. However,there is only a small percentage of data that > changes on a frequent basis. > > I've been looking around and come across pyerplica, londiste and bucardo - > the documentation on most of these is fairly sparse. It seems that Bucardo > may be the best bet - at least initially. You might have a look at Mammoth Replicator (I'm a developer of it). Although we haven't checked whether it works with hundreds of slaves, in theory it should. The feature that can be useful to your setup is 'batched' updates, designed for slaves that are not constantly connected to the master server. These slaves connect to the replication server for some configurable period of time, get the new data, and disconnect until the next attempt. > > However, I thought I'd see if anyone is doing something similar and what > thoughts there might be out there as to a better way to accomplish this. > > Thanks, > Bryan. -- Alexey Klyukin .commandprompt.com The PostgreSQL Company - Command Prompt, Inc -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] primary key and foreign keys
Hi folks, I am trying to do a little bit of changes in one of my DBs, basically it involves changing primary keys to just unique index (the columns are already not null); I was thinking about just dropping Pk in transaction, creating unique index, and creating PK on other column), but I can't do this - due to FK hanging off the PK. Now, what would be the best way to go around it, without need to drop and recreate FKs ? -- GJ
[GENERAL] SqlDataSource and npsql
Hi all, since a while I try to configure npgsql as a .NET data provider for SqlDataSource control in Visual Studio 2008 prof.. Npsql does not appear in the drop down list of data providers. I have done the following configurations: machine.config: web.config (C:\Windows\Microsoft.NET\Framework\v2.0.50727\CONFIG): Is the error on my side or is it impossible what I am trying to do? See also pgFoundry feature request "[#1000654] Add Support for SqlDatasource in ASP.NET 2.0". Thanks a lot for any help on this. Best Regards Oliver -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Understanding 'could not read block'
Hi, I have a table of about 693 million rows (80gb) of position data (standard object,timestamp,position,etc). Every time I try to build some statistics by creating a table, such as: > create table pos_stats1 as > select id,year,month,count(1) from positions group by id,year,month; I get an error: > ERROR: could not read block 8519713 of temporary file: Permission denied I get a similar error sometimes on indexes. What problem am I hitting here? The drive and permissions all seem fine & the query runs for a long time before hitting this error. I've turned off virus scan & it seems to have no effect. Thanks. -- Windows Server 2003 PostgreSQL Version 8.3.0 -- View this message in context: http://www.nabble.com/Understanding-%27could-not-read-block%27-tp25563135p25563135.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- 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] Understanding 'could not read block'
On Thu, Sep 24, 2009 at 1:14 PM, stevesub wrote: > > Hi, > > I have a table of about 693 million rows (80gb) of position data (standard > object,timestamp,position,etc). > > Every time I try to build some statistics by creating a table, such as: > > create table pos_stats1 as > > select id,year,month,count(1) from positions group by id,year,month; > > I get an error: > > ERROR: could not read block 8519713 of temporary file: Permission denied > > I get a similar error sometimes on indexes. What problem am I hitting > here? > The drive and permissions all seem fine & the query runs for a long time > before hitting this error. I've turned off virus scan & it seems to have > no > effect. > > Thanks. > > -- > Windows Server 2003 > PostgreSQL Version 8.3.0 Upgrade to 8.3.8 try switching off any antivirus software running. -- GJ
[GENERAL] Intermediate values and unprivileged users
My apologies if this is an FAQ or considered too general. I have a query like this which returns a single result: SELECT ( (SELECT avg(rel_pressure) as avg4 FROM weather WHERE now() - datetime <= '4 hours' ) - (SELECT avg(rel_pressure) as avg24 FROM weather WHERE now() - datetime <= '24 hours' ) ) AS diff; What I want to be able to do is have a slightly more complex query like this: SELECT ( (SELECT avg(rel_pressure) AS avg4 FROM weather WHERE now() - datetime <= '4 hours' ) - (SELECT avg(rel_pressure) AS avg24 FROM weather WHERE now() - datetime <= '24 hours' ) ) AS diff, CASE WHEN diff < -0.1 THEN 'Falling' WHEN diff > 0.1 THEN 'Rising' ELSE 'Stable' END AS tendency; i.e. the result should be a single row with two columns. Unfortunately all my attempts so far tell me that column "diff" does not exist. Now in most cases I could wing it using a view or temporary table, but in the current one users of the database will not have creation rights: they have to get their queries right or (eventually) use a high-level language. Is there a "good" way to get round this? Server is 8.2 on Linux x86, queries from psql. -- Mark Morgan Lloyd markMLl .AT. telemetry.co .DOT. uk [Opinions above are the author's, not those of his employers or colleagues] -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] What is the difference of foreign key?
To be precise, The difference between create table a ( foreign key (id) referenced by b(name), ); create table b( id integer referenced by b(name), );
Re: [GENERAL] Intermediate values and unprivileged users
Use subselect... SELECT f.foo, (CASE WHEN f.foo > 'something' THEN ...) FROM ( SELECT ) f;
[GENERAL] What is the difference of foreign key?
To be precise, The difference between create table a ( foreign key (id) referenced by b(name), ); create table b( id integer referenced by b(name), );
Re: [GENERAL] What is the difference of foreign key?
Hi, Read http://www.postgresql.org/docs/8.4/static/sql-createtable.html > REFERENCES *reftable* [ ( *refcolumn* ) ] [ MATCH *matchtype* ] [ ON > DELETE *action* ] [ ON UPDATE *action* ] (column constraint) > FOREIGN KEY ( *column* [, ... ] ) REFERENCES *reftable* [ ( *refcolumn* [, > ... ] ) ] [ MATCH *matchtype* ] [ ON DELETE *action* ] [ ON UPDATE *action > * ] (table constraint) HTH Regards, Serge Fonville On Thu, Sep 24, 2009 at 4:18 PM, 纪晓曦 wrote: > To be precise, > > The difference between > > create table a ( > foreign key (id) referenced by b(name), > ); > > create table b( > id integer referenced by b(name), > ); > -- Convince Google!! They need to support Adsense over SSL https://www.google.com/adsense/support/bin/answer.py?hl=en&answer=10528 http://www.google.bg/support/forum/p/AdSense/thread?tid=1884bc9310d9f923&hl=en
Re: [GENERAL] Looking for way to replicate master db to multiple mobile databases
Hi! On Wed, Sep 23, 2009 at 10:11 AM, Bryan Montgomery wrote: > Hi, > I'm looking for a way to replicate am master database to multiple (100+) > databases that are taken in to the field. Currently for each laptop we dump > and load the tables. However,there is only a small percentage of data that > changes on a frequent basis. > > I've been looking around and come across pyerplica, londiste and bucardo - > the documentation on most of these is fairly sparse. It seems that Bucardo > may be the best bet - at least initially. Bucardo is a good choice for this usage model because it was originally designed to work over a lossy network connections. You could issue 'kicks' for each laptop sync when you know for sure that a laptop has got an active network connection to your master. It's also pretty efficient with updates, only copying the current row (that's changed) a single time, rather than multiple times if there have been multiple changes to that row since the last time a sync occurred. -selena -- http://chesnok.com/daily - me http://endpoint.com - work -- 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] Understanding 'could not read block'
=?UTF-8?Q?Grzegorz_Ja=C5=9Bkiewicz?= writes: > On Thu, Sep 24, 2009 at 1:14 PM, stevesub wrote: >> I get an error: >> ERROR: could not read block 8519713 of temporary file: Permission denied > try switching off any antivirus software running. The usual advice is to actually *uninstall* whatever antivirus software you are running. Some of it is so broken that it keeps on rejecting things at random even when nominally turned off. My private advice is to get off Windows. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] What is the difference of these two kinds of foreign key defination?
To be precise, The difference between create table a ( foreign key (id) referenced by b(name), ); create table b( id integer referenced by b(name), );
Re: [GENERAL] Looking for way to replicate master db to multiple mobile databases
Le jeudi 24 septembre 2009, Selena Deckelmann a écrit : > Hi! > > On Wed, Sep 23, 2009 at 10:11 AM, Bryan Montgomery wrote: > > Hi, > > I'm looking for a way to replicate am master database to multiple (100+) > > databases that are taken in to the field. Currently for each laptop we > > dump and load the tables. However,there is only a small percentage of > > data that changes on a frequent basis. > > > > I've been looking around and come across pyerplica, londiste and bucardo > > - the documentation on most of these is fairly sparse. It seems that > > Bucardo may be the best bet - at least initially. > > Bucardo is a good choice for this usage model because it was > originally designed to work over a lossy network connections. yes, but isn't bucardo designed to 2 nodes only ? > > You could issue 'kicks' for each laptop sync when you know for sure > that a laptop has got an active network connection to your master. > It's also pretty efficient with updates, only copying the current row > (that's changed) a single time, rather than multiple times if there > have been multiple changes to that row since the last time a sync > occurred. > > -selena > -- Cédric Villemain Administrateur de Base de Données Cel: +33 (0)6 74 15 56 53 http://dalibo.com - http://dalibo.org signature.asc Description: This is a digitally signed message part.
Re: [GENERAL] Looking for way to replicate master db to multiple mobile databases
On Thu, Sep 24, 2009 at 05:09:26PM +0200, Cédric Villemain wrote: > > Bucardo is a good choice for this usage model because it was > > originally designed to work over a lossy network connections. > > yes, but isn't bucardo designed to 2 nodes only ? Bucardo's multi-master replication works only between two hosts, unless perhaps you can assure that only certain primary keys will be updated on certain hosts, or do some other trickery. Syncing from one master to multiple slaves is straightforward, if all you need is master->slave. -- Joshua Tolley / eggyknap End Point Corporation http://www.endpoint.com signature.asc Description: Digital signature
[GENERAL] repeatedly process termination in PG 8.2
Hi there, one of our systems on a PG 8.2 database crashes nearly daily; others are running fine. Host OS of this system is Windows, if the db crashes nothing is written in the OS event logs. The database log show every time the following messages: 2009-09-23 10:21:30 LOG: server process (PID 1240) exited with exit code -1073741819 2009-09-23 10:21:30 LOG: terminating any other active server processes 2009-09-23 10:21:30 WARNING: terminating connection because of crash of another server process 2009-09-23 10:21:30 DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. 2009-09-23 10:21:30 HINT: In a moment you should be able to reconnect to the database and repeat your command. 2009-09-23 10:21:30 WARNING: terminating connection because of crash of another server process 2009-09-23 10:21:30 DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. 2009-09-23 10:21:30 HINT: In a moment you should be able to reconnect to the database and repeat your command. 2009-09-23 10:21:30 WARNING: terminating connection because of crash of another server process 2009-09-23 10:21:30 DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. 2009-09-23 10:21:30 HINT: In a moment you should be able to reconnect to the database and repeat your command. 2009-09-23 10:21:30 WARNING: terminating connection because of crash of another server process 2009-09-23 10:21:30 DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. 2009-09-23 10:21:30 HINT: In a moment you should be able to reconnect to the database and repeat your command. 2009-09-23 10:21:30 WARNING: terminating connection because of crash of another server process 2009-09-23 10:21:30 DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. 2009-09-23 10:21:30 HINT: In a moment you should be able to reconnect to the database and repeat your command. 2009-09-23 10:21:30 WARNING: terminating connection because of crash of another server process 2009-09-23 10:21:30 DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. 2009-09-23 10:21:30 HINT: In a moment you should be able to reconnect to the database and repeat your command. 2009-09-23 10:21:30 WARNING: terminating connection because of crash of another server process 2009-09-23 10:21:30 DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. 2009-09-23 10:21:30 HINT: In a moment you should be able to reconnect to the database and repeat your command. 2009-09-23 10:21:32 LOG: all server processes terminated; reinitializing The exit code in the first log line is the same on every crash even. But I could not find anything about it on the net. Other applications on the same system doesn´t show notes that reference to hardware based causes. May anyone give me a hint what´s wrong with this system or what we can do to avoid further crashes? Regards Michael -- Diese Mail wurde auf Viren geprüft mit Hilfe von Astaro Mail Gateway. http://www.astaro.com -- 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]
>What query, exactly? Which PG version is this? > regards, tom lane Its a query involving 15 relations, processing not more than 500 records. The query is written in a .sql file and the file is executed as \i initq.sql Infact, know no .sql file is getting executed. All my .sqls have >=15 records. The error message is: psql: initq.sql:64: server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. psql: initq.sql:64: connection to server was lost and the logfile shows: LOG: server process (PID 3304) was terminated by exception 0xC005 HINT: See C include file "ntstatus.h" for a description of the hexadecimal value. LOG: terminating any other active server processes LOG: all server processes terminated; reinitializing LOG: database system was interrupted; last known up at 2009-09-24 18:01:00 IST LOG: database system was not properly shut down; automatic recovery in progress LOG: record with zero length at 0/1A71AE0 LOG: redo is not required LOG: database system is ready to accept connections LOG: autovacuum launcher started The execution for this and other queries has been fine so far. What is exception 0xC005? I am working on Windows XP. Have built version 8.4.0 from source using Visual Studio 2005. Thanks and Regards Swati __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
Re: [GENERAL] How to have ant's task insert special chars appropriately?
Hi Richard, thanks for the answer! Nevertheless, see below... ;) > Richard Huxton wrote: > agostonbejo wrote: > > > > Hi! > > > > What I'm trying to do is to insert some data from a sql file into a > postgres > > DB by calling the ant task. My problem is that I can't get special > > characters (even if they can be represented by the standard ASCII > charset, > > such as ä, ö, ü, é, etc.) to be inserted correctly. > > Those aren't ASCII. OK, probably my idea of what ASCII is is a bit too vague: by ASCII I simply meant the ISO-8859-1 charset. (Which might make further discussions about what exactly belongs to ASCII unnecessary...?) Eclipse (the editor which I'm using) says that the original SQL file's encoding is ISO-8859-1, the special characters are shown correctly, also in other text editors. > > There are three places you need to get this right: > 1. The database encoding > 2. The client encoding > 3. The encoding of the contents of the .sql file > > Now, since the database is UTF8 that means it can accept the entire > range of unicode characters, including all ISO-8859-1. > > PostgreSQL can automatically convert from ISO-8859-1 to UTF-8 for you, > so it doesn't matter which you have in your .sql file. > > What *does* matter is that you know what encoding your .sql file is > using and that you set the client encoding appropriately. How do I set the client encoding to ISO-8859-1? As I wrote, the task complains if I set the client encoding to LATIN1 (which is the PostGres equivalent of ISO-8859-1 if I'm right) that the JDBC driver is not going to like it. (And so it seems indeed.) > > Since you're using Java, it's probably simplest just to use UTF-8 all > the way through. Crucially, make sure you know what the character-set of > the .sql file is - any good text editor should be able to tell you / set > this. As I wrote in my original post, I *have* tried using UTF-8 "all the way through" by converting the original ISO-8859-1 file to UTF-8 and calling the task with 'encoding="UTF-8"'. It didn't help, the special characters still became question marks. I've also set the client_encoding parameter in the sql file explicitly and I know, i.e., pgAdmin tells me the DB's encoding is UTF-8. (And it should be right, since *that* is able to insert special characters) So, to my best knowledge I got it right on all three places, and it still doesn't work. That's why I opened the topic in the first place. So any other idea what can be wrong here? Thanks! Agoston > > -- > Richard Huxton > Archonet Ltd > -- View this message in context: http://www.nabble.com/How-to-have-ant%27s-%3Csql%3E-task-insert-special-chars-appropriately--tp25530663p25577683.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Exception 0xC0000005
Sorry for resending, it got stalled. Have subscribed to pgsql-general now. >What query, exactly? Which PG version is this? > regards, tom lane Its a query involving 15 relations, processing not more than 500 records. The query is written in a .sql file and the file is executed as \i initq.sql Infact, know no .sql file is getting executed. All my .sqls have >=15 records. The error message is: psql: initq.sql:64: server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. psql: initq.sql:64: connection to server was lost and the logfile shows: LOG: server process (PID 3304) was terminated by exception 0xC005 HINT: See C include file "ntstatus.h" for a description of the hexadecimal value. LOG: terminating any other active server processes LOG: all server processes terminated; reinitializing LOG: database system was interrupted; last known up at 2009-09-24 18:01:00 IST LOG: database system was not properly shut down; automatic recovery in progress LOG: record with zero length at 0/1A71AE0 LOG: redo is not required LOG: database system is ready to accept connections LOG: autovacuum launcher started The execution for this and other queries has been fine so far. What is exception 0xC005? I am working on Windows XP. Have built version 8.4.0 from source using Visual Studio 2005. Thanks and Regards Swati
[GENERAL] generic modelling of data models; enforcing constraints dynamically...
Dear List, I am trying to implement the following: In a database I wish to implement a GENERIC datamodel, thus on a meta-level. All RELATIONS (part of a MODEL) will be a view on some base (being a table) JOINed with (an) extra column(s). Thus, this view consists of a number of FIELDS. I whish to make this view editable (INSERT, UPDATE) using the RULE system. Some constraints will apply; enforcing these is the problem I am trying to solve by modeling these rules with a table "RELATION_CONSTRAINTS" (see below). Tables: BASE(col1, col2, col3) MODELS(name) RELATIONS(modelname, name) FIELDS(modelname, relation_name, name, datatype) RELATION_CONSTRAINTS(modelname, relation_name, constraint_name, constraining_expression) I was thinking of implementing this using a FUNCTION that takes a polymorphic record parameter (and the relation name); then checking this record against the applicable constraint expression. This whole idea may sound like a DBMS-in-a-DBMS kind of thing... What I am trying is to write as little as table/view-specific code as would be necessary, while still collecting all base data in one central table... All suggestions are very much appreciated, regards, Rob
Re: [GENERAL] Exception 0xC0000005
On Thu, Sep 24, 2009 at 12:22 PM, Ms swati chande wrote: > > Sorry for resending, it got stalled. > Have subscribed to pgsql-general now. > > >What query, exactly? Which PG version is this? > > regards, tom lane > Its a query involving 15 relations, processing not more than 500 records. The > query is written in a .sql file and the file is executed as > > \i initq.sql > > Infact, know no .sql file is getting executed. All my .sqls have >=15 records. > > The error message is: > psql: initq.sql:64: server closed the connection unexpectedly > This probably means the server terminated abnormally > before or while processing the request. > psql: initq.sql:64: connection to server was lost > > and the logfile shows: > > > LOG: server process (PID 3304) was terminated by exception 0xC005 > > HINT: See C include file "ntstatus.h" for a description of the hexadecimal > value. > > LOG: terminating any other active server processes > > LOG: all server processes terminated; reinitializing > > LOG: database system was interrupted; last known up at 2009-09-24 18:01:00 > IST > > LOG: database system was not properly shut down; automatic recovery in > progress > > LOG: record with zero length at 0/1A71AE0 > > LOG: redo is not required > > LOG: database system is ready to accept connections > > LOG: autovacuum launcher started > > > > The execution for this and other queries has been fine so far. > > What is exception 0xC005? > > I am working on Windows XP. > > Have built version 8.4.0 from source using Visual Studio 2005. 1. try upgrading to 8.4.1. There was at least one known crash (involving aggregate functions) that was fixed in 8.4.0 2. if that doesn't help, try and reduce your .sql file to a test case and send it along. merlin -- 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] Exception 0xC0000005
On Thu, Sep 24, 2009 at 5:22 PM, Ms swati chande wrote: > > What is exception 0xC005? > > SIGSEGV in micro$oft world. -- GJ
Re: [GENERAL] Exception 0xC0000005
>SIGSEGV in micro$oft world. Thanks for your response. How can this be resolved? Regards Swati
Re: [GENERAL] Exception 0xC0000005
On Thu, Sep 24, 2009 at 5:34 PM, Ms swati chande wrote: > >SIGSEGV in micro$oft world. > > Thanks for your response. > How can this be resolved? > > Just like others said, upgrade to 8.4.1 first. -- GJ
Re: [GENERAL] generic modelling of data models; enforcing constraints dynamically...
InterRob wrote: Dear List, I am trying to implement the following: [snip] All suggestions are very much appreciated, regards, Rob It's not clear to me what you're asking, but I suspect the suggestion you need is the same as if you had asked how to best implement an Entity-Attribute-Value scheme: don't do it. Why it may be possible, performance is going to go into the toilet, constraints are going to be difficult to enforce, and maintenance will be difficult at best. Spending the effort upfront to define a schema will have drastic long-term payoffs. It can be tempting to believe an application can define the appropriate schema for itself at runtime if you just give it a big enough sandbox, but this rarely works out well. -- 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] generic modelling of data models; enforcing constraints dynamically...
On Thu, Sep 24, 2009 at 06:28:28PM +0200, InterRob wrote: > I am trying to implement the following: > > In a database I wish to implement a GENERIC datamodel, thus on a meta-level. Sounds like you're describing an EAV design: http://en.wikipedia.org/wiki/Entity-attribute-value_model Designs like this tend to result in you getting very little support from the database and get awkward as they grow. If your problem really is suited to this then go for it, but surprisingly few actually are. I'd highly recommend using a more traditional design until you've been through at least one big revision and then you'll know whether EAV really fits. -- Sam http://samason.me.uk/ -- 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] How to have ant's task insert special chars appropriately?
agostonbejo wrote: > > > Hi Richard, > > thanks for the answer! Nevertheless, see below... ;) > >> Richard Huxton wrote: >> agostonbejo wrote: >>> Hi! >>> >>> What I'm trying to do is to insert some data from a sql file into a >> postgres >>> DB by calling the ant task. My problem is that I can't get special >>> characters (even if they can be represented by the standard ASCII >> charset, >>> such as ä, ö, ü, é, etc.) to be inserted correctly. >> Those aren't ASCII. > > OK, probably my idea of what ASCII is is a bit too vague: by ASCII I simply > meant the ISO-8859-1 charset. (Which might make further discussions about > what exactly belongs to ASCII unnecessary...?) > > Eclipse (the editor which I'm using) says that the original SQL file's > encoding is ISO-8859-1, the special characters are shown correctly, also in > other text editors. OK. >> There are three places you need to get this right: >> 1. The database encoding >> 2. The client encoding >> 3. The encoding of the contents of the .sql file >> >> Now, since the database is UTF8 that means it can accept the entire >> range of unicode characters, including all ISO-8859-1. >> >> PostgreSQL can automatically convert from ISO-8859-1 to UTF-8 for you, >> so it doesn't matter which you have in your .sql file. >> >> What *does* matter is that you know what encoding your .sql file is >> using and that you set the client encoding appropriately. > > How do I set the client encoding to ISO-8859-1? As I wrote, the task > complains if I set the client encoding to LATIN1 (which is the PostGres > equivalent of ISO-8859-1 if I'm right) that the JDBC driver is not going to > like it. (And so it seems indeed.) Correct LATIN1 == ISO-8859-1. Can't help with the JDBC. >> Since you're using Java, it's probably simplest just to use UTF-8 all >> the way through. Crucially, make sure you know what the character-set of >> the .sql file is - any good text editor should be able to tell you / set >> this. > > As I wrote in my original post, I *have* tried using UTF-8 "all the way > through" by converting the original ISO-8859-1 file to UTF-8 and calling the > task with 'encoding="UTF-8"'. It didn't help, the special characters > still became question marks. I've also set the client_encoding parameter in > the sql file explicitly and I know, i.e., pgAdmin tells me the DB's encoding > is UTF-8. (And it should be right, since *that* is able to insert special > characters) > > So, to my best knowledge I got it right on all three places, and it still > doesn't work. That's why I opened the topic in the first place. Check again - something isn't right. Take the original ,sql file, save it as UTF8 and add a line at the top "set client_encoding=utf8;" Run this through psql and it should work fine. If not, then the database isn't in utf8 after all. Assuming it works, then something in your java setup isn't correct. -- Richard Huxton Archonet Ltd -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Partitioned table question
Assuming the examples on http://www.postgresql.org/docs/8.3/static/ddl-partitioning.html where measurement has children as noted CREATE TABLE measurement ( city_id int not null, logdate date not null, peaktempint, unitsales int ); CREATE TABLE measurement_y2006m02 ( ) INHERITS (measurement); CREATE TABLE measurement_y2006m03 ( ) INHERITS (measurement); ... CREATE TABLE measurement_y2007m11 ( ) INHERITS (measurement); CREATE TABLE measurement_y2007m12 ( ) INHERITS (measurement); CREATE TABLE measurement_y2008m01 ( ) INHERITS (measurement); and insertion is governed ala the trigger example CREATE TRIGGER insert_measurement_trigger BEFORE INSERT ON measurement FOR EACH ROW EXECUTE PROCEDURE measurement_insert_trigger(); CREATE OR REPLACE FUNCTION measurement_insert_trigger() RETURNS TRIGGER AS $$ BEGIN IF ( NEW.logdate >= DATE '2006-02-01' AND NEW.logdate < DATE '2006-03-01' ) THEN INSERT INTO measurement_y2006m02 VALUES (NEW.*); ELSIF ( NEW.logdate >= DATE '2006-03-01' AND NEW.logdate < DATE '2006-04-01' ) THEN INSERT INTO measurement_y2006m03 VALUES (NEW.*); ... ELSIF ( NEW.logdate >= DATE '2008-01-01' AND NEW.logdate < DATE '2008-02-01' ) THEN INSERT INTO measurement_y2008m01 VALUES (NEW.*); ELSE RAISE EXCEPTION 'Date out of range. Fix the measurement_insert_trigger() function!'; END IF; RETURN NULL; END; $$ LANGUAGE plpgsql; If I know that no incoming data will be going into measurement_y2007m11 and I NO INHERIT measurement_y2007m11 from measurement, do I have to immediately update the function measurement_insert_trigger() to remove references to measurement_y2007m11, or will the function continue to work fine and I can update it when convenient? Thanks, reid -- 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] lazy vacuum and AccessExclusiveLock
Jaromír Talíř wrote: > we are facing strange situation with exclusively locked table during > normal lazy vacuum. There is one big table (66GB) that is heavily > inserted and updated in our database. Suddenly (after backup and delete > of almost all records) we are not able to run VACUUM over this table > because after 50 minutes of work it allocate AccessExclusiveLock on this > table and all other connections start to timeout. What version are you running? -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Newbie's question: How can I connect to my postgresql-server?
Dear my friends I am still new in PostgreSQL. Usually I use MySQL on OpenSuSE. I just have installed postgres with: "zypper install postgresql-server". The installation looked OK. As post-installation process, I've done: " sussy:~ # rcpostgresql status Checking for PostgreSQL: running sussy:~ # su postgres postg...@sussy:/root> createuser -D -p ricky could not change directory to "/root" Enter name of role to add: ricky Shall the new role be a superuser? (y/n) y createuser: could not connect to database postgres: could not connect to server: No such file or directory Is the server running locally and accepting connections on Unix domain socket "/tmp/.s.PGSQL.0"? postg...@sussy:/root> ". Why can I not connect to my Postgres? Please tell me. Give me a first hint and this small step for me to getting start to use postgres. Thank you very much in advance. -- 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] pgadmin is changing pgpass.conf
Howard Cole wrote: > Not in this case. There are originally two entries in pgpass.conf - > one for server localhost and one for server 127.0.0.1 - the > reasoning behind this is that when the backup runs as a scheduled > task it sometimes seems to prefer one format to the other. However, > when I open PGAdmin, one of the entries disappears. Perhaps it > resolves the address and thinks they are the same entries? I think you should file this as a pgadmin bug -- see the pgadmin lists for that. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- 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] Newbie's question: How can I connect to my postgresql-server?
On Fri, Sep 25, 2009 at 01:47:03AM +0700, Ricky Tompu Breaky wrote: > I am still new in PostgreSQL. Usually I use MySQL on OpenSuSE. Welcome over, PG is *normally* much better behaved than this and generally gives very good error messages. > postg...@sussy:/root> createuser -D -p ricky I don't think you want to be passing "-p" here; it's saying to use "ricky" as the port number, which fails (sounds like a bug if it doesn't complain about this) giving a port number of zero, which is why you see this: > connections on Unix domain socket "/tmp/.s.PGSQL.0"? the ".0" is normally something like ".5432". Maybe you want "-P" to be asking for the password? -- Sam http://samason.me.uk/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] About logging
Is it possible to log two different information to two different file. Bascially i need to log all the mod statement in one log csv file and all the queries running more then 2mins in another csv log file. As i enabled both it will be doing both in single file rt . Is there any way to split both???
Re: [GENERAL] About logging
On Thu, Sep 24, 2009 at 21:06, wrote: > Is it possible to log two different information to two different file. > Bascially i need to log all the mod statement in one log csv file and all > the queries running more then 2mins in another csv log file. As i enabled > both it will be doing both in single file rt . Is there any way to split > both??? This is not currently possible inside PostgreSQL, you will need to do external post-processing of the logfile to get that. -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] v8.1 pgbench ERRORs
Okay, don't flame me for (still) using Postgres v8.1.10, but we were running pgbench on new hardware today, and in the middle of like 50 various runs of pgbench, we saw this: pgbench -c 20 -t 500 pgbench starting vacuum...end. Client 4 aborted in state 8: ERROR: integer out of range Client 3 aborted in state 8: ERROR: integer out of range Client 17 aborted in state 8: ERROR: integer out of range Client 1 aborted in state 8: ERROR: integer out of range Client 13 aborted in state 8: ERROR: integer out of range Client 7 aborted in state 8: ERROR: integer out of range Client 2 aborted in state 8: ERROR: integer out of range Client 19 aborted in state 8: ERROR: integer out of range Client 9 aborted in state 8: ERROR: integer out of range Client 16 aborted in state 8: ERROR: integer out of range Client 12 aborted in state 8: ERROR: integer out of range Client 15 aborted in state 8: ERROR: integer out of range Client 14 aborted in state 8: ERROR: integer out of range Client 18 aborted in state 8: ERROR: integer out of range Client 5 aborted in state 8: ERROR: integer out of range Client 10 aborted in state 8: ERROR: integer out of range Client 6 aborted in state 8: ERROR: integer out of range Client 8 aborted in state 8: ERROR: integer out of range Client 11 aborted in state 8: ERROR: integer out of range Client 0 aborted in state 8: ERROR: integer out of range transaction type: TPC-B (sort of) scaling factor: 20 number of clients: 20 number of transactions per client: 500 number of transactions actually processed: 305/1 tps = 734.580434 (including connections establishing) tps = 819.883657 (excluding connections establishing) After reinitializing the pgbench database, everything is running fine. Anyone seen this before? Could it really be related to an old version of PG, whose source isn't even downloadable anymore? Or would this point to hardware problems? eric -- 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] Partitioned table question
On Thu, Sep 24, 2009 at 12:02 PM, Reid Thompson wrote: > Assuming the examples on > http://www.postgresql.org/docs/8.3/static/ddl-partitioning.html > > where measurement has children as noted > > CREATE TABLE measurement ( > city_id int not null, > logdate date not null, > peaktemp int, > unitsales int > ); > > CREATE TABLE measurement_y2006m02 ( ) INHERITS (measurement); > CREATE TABLE measurement_y2006m03 ( ) INHERITS (measurement); > ... > CREATE TABLE measurement_y2007m11 ( ) INHERITS (measurement); > CREATE TABLE measurement_y2007m12 ( ) INHERITS (measurement); > CREATE TABLE measurement_y2008m01 ( ) INHERITS (measurement); > > and insertion is governed ala the trigger example > > CREATE TRIGGER insert_measurement_trigger > BEFORE INSERT ON measurement > FOR EACH ROW EXECUTE PROCEDURE measurement_insert_trigger(); > > CREATE OR REPLACE FUNCTION measurement_insert_trigger() > RETURNS TRIGGER AS $$ > BEGIN > IF ( NEW.logdate >= DATE '2006-02-01' AND NEW.logdate < DATE > '2006-03-01' ) THEN > INSERT INTO measurement_y2006m02 VALUES (NEW.*); > ELSIF ( NEW.logdate >= DATE '2006-03-01' AND NEW.logdate < DATE > '2006-04-01' ) THEN > INSERT INTO measurement_y2006m03 VALUES (NEW.*); > ... > ELSIF ( NEW.logdate >= DATE '2008-01-01' AND NEW.logdate < DATE > '2008-02-01' ) THEN > INSERT INTO measurement_y2008m01 VALUES (NEW.*); > ELSE > RAISE EXCEPTION 'Date out of range. Fix the > measurement_insert_trigger() function!'; > END IF; > RETURN NULL; > END; > $$ > LANGUAGE plpgsql; > > > If I know that no incoming data will be going into measurement_y2007m11 > and I NO INHERIT measurement_y2007m11 from measurement, do I have to > immediately update the function measurement_insert_trigger() to remove > references to measurement_y2007m11, or will the function continue to > work fine and I can update it when convenient? You can update it whenever it's convenient. I have a similar set and I drop and recreate the insert trigger every night to handle inserts for all past partitions and into the future 30 days. So if it fails for a night or two no great loss. You can test inserting with a large trigger and all the partitions and on one that only hits maybe a few days in the past and a few days in the future to see if it's faster on your machine. On mine there's no big difference up to a few hundred tables at lease. -- 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] Newbie's question: How can I connect to my postgresql-server?
On Thu, 24 Sep 2009 19:57:55 +0100 Sam Mason wrote: > On Fri, Sep 25, 2009 at 01:47:03AM +0700, Ricky Tompu Breaky wrote: > > I am still new in PostgreSQL. Usually I use MySQL on OpenSuSE. > > Welcome over, PG is *normally* much better behaved than this and > generally gives very good error messages. > > > postg...@sussy:/root> createuser -D -p ricky > > I don't think you want to be passing "-p" here; it's saying to use > "ricky" as the port number, which fails (sounds like a bug if it > doesn't complain about this) giving a port number of zero, which is > why you see this: > > > connections on Unix domain socket "/tmp/.s.PGSQL.0"? > > the ".0" is normally something like ".5432". > > Maybe you want "-P" to be asking for the password? > Dear Sam. Thank you very much for your help. You've solved my problem. But I still can not login to my PostgreSQL-Server. Here what I've done: " postg...@sussy:/root> createuser -D -P ricky could not change directory to "/root" Enter password for new role: Enter it again: Shall the new role be a superuser? (y/n) y postg...@sussy:/root> sussy:~ # rcpostgresql restart Shutting down PostgreSQLServer angehalten done Starting PostgreSQL done sussy:~ # psql -U ricky -W Password for user ricky: psql: FATAL: Ident-Authentifizierung für Benutzer »ricky« fehlgeschlagen (I translate it: psql: FATAL: Ident-Authentication for user »ricky« failed) sussy:~ # ". I'm sure that I used the correct password as "createuser" the account of 'ricky'. Please keep telling me. Thank you very much in advance. -- 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] generic modelling of data models; enforcing constraints dynamically...
Thank you, Ben. Well, I'm afraid you got the basic idea... I intend to
implement a hybrid between a fixed schema and an Entity-Attribute-Value
scheme. The schema will be able to cover 90% of the data needs; in other
cases (specific projects) additional fields (and/or tables/relations) will
be needed; including their constraints...
I'm experienting now with some smart thought that just came up: passing a
set of key/value pairs to function that will test the new row; on insert /
update the following could then be checked (as part of a RULE-set):
SELECT doesComply('relationname', keyValues.*) FROM (VALUES('col1',
CAST(col1 AS TEXT)), VALUES('col2', CAST(col2 AS TEXT))) AS
keyValues(the_key, the_value);
The function "doesComply()" will then process the CONSTRAINTS table and
raise an Error if the new / updated row does not fit...
Any thoughts?
Rob
2009/9/24 Ben Chobot
> InterRob wrote:
>
>> Dear List,
>>
>> I am trying to implement the following:
>>
>> [snip]
>>
>> All suggestions are very much appreciated,
>> regards,
>>
>>
>> Rob
>>
>>
>>
> It's not clear to me what you're asking, but I suspect the suggestion you
> need is the same as if you had asked how to best implement an
> Entity-Attribute-Value scheme: don't do it. Why it may be possible,
> performance is going to go into the toilet, constraints are going to be
> difficult to enforce, and maintenance will be difficult at best. Spending
> the effort upfront to define a schema will have drastic long-term payoffs.
> It can be tempting to believe an application can define the appropriate
> schema for itself at runtime if you just give it a big enough sandbox, but
> this rarely works out well.
>
>
[GENERAL] libpq port number handling
On Thu, Sep 24, 2009 at 07:57:55PM +0100, Sam Mason wrote:
> > postg...@sussy:/root> createuser -D -p ricky
>
> I don't think you want to be passing "-p" here; it's saying to use
> "ricky" as the port number, which fails (sounds like a bug if it doesn't
> complain about this) giving a port number of zero
Hum, why is PG doing an (unchecked) atoi on the user specified port
rather than leaving it up to getaddrinfo to resolve the port? It would
seem to require changing UNIXSOCK_PATH to accept a string as the "port
number", which is probably a bit much of a change.
The included doesn't feel very nice, but is probably more acceptable.
--
Sam http://samason.me.uk/
--- src/interfaces/libpq/fe-connect.c~ 2009-06-11 15:49:13.0 +0100
+++ src/interfaces/libpq/fe-connect.c 2009-09-24 20:48:53.0 +0100
@@ -817,7 +817,16 @@
/* Set up port number as a string */
if (conn->pgport != NULL && conn->pgport[0] != '\0')
+ {
portnum = atoi(conn->pgport);
+ if (portnum < 1 || portnum > 65535)
+ {
+ appendPQExpBuffer(&conn->errorMessage,
+ libpq_gettext("invalid port number \"%s\" specified\n"),
+ conn->pgport);
+ goto connect_errReturn;
+ }
+ }
else
portnum = DEF_PGPORT;
snprintf(portstr, sizeof(portstr), "%d", portnum);
--
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] Newbie's question: How can I connect to my postgresql-server?
On Fri, Sep 25, 2009 at 02:47:23AM +0700, Ricky Tompu Breaky wrote: > I'm sure that I used the correct password as "createuser" the > account of 'ricky'. By default; PG is set up not to use passwords in the "local" case. This is what the "ident" in the error is about. Your PG username needs to be the same as your Unix username or ident authentication will fail. Have a look at: http://www.postgresql.org/docs/current/interactive/auth-pg-hba-conf.html if you want to change it to use passwords as the authentication method in the local case. Note, that you don't need to restart PG when adding/removing users, but you do when changing the config files like pg_hba.conf and postgresql.conf. It's a "inside"/"outside" PG thing, users are stored in the database and hence PG knows when they're added/removed, but the config files are outside and PG doesn't know when to go looking for changes. There are plans to change this for pg_hba.conf, but you'll have to wait a bit before that happens. -- Sam http://samason.me.uk/ -- 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] generic modelling of data models; enforcing constraints dynamically...
Rob Marjot wrote: Thank you, Ben. Well, I'm afraid you got the basic idea... I intend to implement a hybrid between a fixed schema and an Entity-Attribute-Value scheme. The schema will be able to cover 90% of the data needs; in other cases (specific projects) additional fields (and/or tables/relations) will be needed; including their constraints... If you absolutely must have a dynamic schema like this, and can't have a DBA simply add tables as needed, then I think it would be less work, overall, to create a schema that your application has DDL rights to, and then let it create and modify normal tables with normal constraints there. There certainly are some cases where an EAV solution is the proper one, and yours may be one of them. But most aren't. -- 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] Log File Melancholy
On Sep 23, 2009, at 7:46 PM, Tom Lane wrote: Jerry LeVan writes: could not lookup DNS configuration info service: (ipc/send) invalid destination port LOG: could not resolve "localhost": nodename nor servname provided, or not known LOG: disabling statistics collector for lack of working socket I cannot figure out where the "could not lookup DNS..." line is coming from... Presumably from inside getaddrinfo(), since the subsequent LOG message is Postgres complaining that getaddrinfo didn't work. Am I unique? Works for me on both 32- and 64-bit Snow Leopard installations, and for at least one Snow Leopard machine in the buildfarm. There must be something funny about the DNS configuration on your machines, but there's no way to tell what from here. regards, tom lane Snow Leopard and Postgresql works for me *finally*. I did a clean install on a spare firewire disk and only added the postgresql stuff. I did a 64bit build, inited the db and imported a dump from the other system. I started the database manually as in the INSTALL note and everything started properly. I just about decided that I needed to do a clean install on my main machine, but I decided to do one more test. I copied the PostgreSQL StartupItem directory from my main machine and rebooted. I got the same errors as I have been complaining about! A few more tweaks and restarts showed I could manually restart postgresql from the command line but I could not start postgresql properly from the StartupItems boot process. I have been using the same startup setup for years and years I suspected that the problem was that the Postgresql system was getting started too early. I installed the osx startup items in the contrib directory and tweeked the startup script to point to the nonstandard place I have the binaries. I now get a clean startup ( Stats collector on and Autovacuum on) and as an extra bonus dblink_connect is again resolving names :) Whew! Jerry -- 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] generic modelling of data models; enforcing constraints dynamically...
On Thu, Sep 24, 2009 at 09:23:35PM +0200, Rob Marjot wrote:
> SELECT doesComply('relationname', keyValues.*) FROM (VALUES('col1',
> CAST(col1 AS TEXT)), VALUES('col2', CAST(col2 AS TEXT))) AS
> keyValues(the_key, the_value);
>
> The function "doesComply()" will then process the CONSTRAINTS table and
> raise an Error if the new / updated row does not fit...
I'd have a set of doesComply functions, the first two parameters
as you have them but overload a set to support different datatypes
specifically. Something like:
CREATE FUNCTION doesComply(_rel TEXT, _key TEXT, _val INT) ...
CREATE FUNCTION doesComply(_rel TEXT, _key TEXT, _val DATE) ...
CREATE FUNCTION doesComply(_rel TEXT, _key TEXT, _val TEXT) ...
CREATE FUNCTION doesComply(_rel TEXT, _key TEXT, _val NUMERIC) ...
And then have a set of "attribute" tables (one for each datatype) to
store the actual values in. At least PG can do some type checking for
you that way. Either that, or just leave them all as text to text
mappings in the database and only attempt to type things out in the
client code.
Not sure why you're doing the VALUES contortions as well, why not just:
SELECT doesComply('relationname', 'col1', col2);
?
--
Sam http://samason.me.uk/
--
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] generic modelling of data models; enforcing constraints dynamically...
Thank you, Ben. Well, I'm afraid you got the basic idea... I intend to
implement a hybrid between a fixed schema and an Entity-Attribute-Value
scheme. The schema will be able to cover 90% of the data needs; in other
cases (specific projects) additional fields (and/or tables/relations) will
be needed; including their constraints...
I'm experienting now with some smart thought that just came up: passing a
set of key/value pairs to function that will test the new row; on insert /
update the following could then be checked (as part of a RULE-set):
SELECT doesComply('relationname', keyValues.*) FROM (VALUES('col1',
CAST(col1 AS TEXT)), VALUES('col2', CAST(col2 AS TEXT))) AS
keyValues(the_key, the_value);
The function "doesComply()" will then process the CONSTRAINTS table and
raise an Error if the new / updated row does not fit...
Any thoughts?
Rob
2009/9/24 Ben Chobot
> Rob Marjot wrote:
>
>> Thank you, Ben. Well, I'm afraid you got the basic idea... I intend to
>> implement a hybrid between a fixed schema and an Entity-Attribute-Value
>> scheme. The schema will be able to cover 90% of the data needs; in other
>> cases (specific projects) additional fields (and/or tables/relations) will
>> be needed; including their constraints...
>>
>
> If you absolutely must have a dynamic schema like this, and can't have a
> DBA simply add tables as needed, then I think it would be less work,
> overall, to create a schema that your application has DDL rights to, and
> then let it create and modify normal tables with normal constraints there.
>
> There certainly are some cases where an EAV solution is the proper one, and
> yours may be one of them. But most aren't.
>
>
Re: [GENERAL] generic modelling of data models; enforcing constraints dynamically...
Sam, Thanks for thinking along.
The thing is that a SINGLE constraint might apply to MULTIPLE fields;
therefore it seems best to build a set of key/value pairs... Multiple
doesComply()s won't do the job :(
BY THE WAY:
I came to think of another option: putting additional columns (that is:
addittional to the default set of fields) in xml, in a column that is part
of row (=object) it belongs to.
Any body has done so before? Any body has experience with XML schema
validation within PostgreSQL?
Cheerz,
Rob
2009/9/24 Sam Mason
> On Thu, Sep 24, 2009 at 09:23:35PM +0200, Rob Marjot wrote:
> > SELECT doesComply('relationname', keyValues.*) FROM (VALUES('col1',
> > CAST(col1 AS TEXT)), VALUES('col2', CAST(col2 AS TEXT))) AS
> > keyValues(the_key, the_value);
> >
> > The function "doesComply()" will then process the CONSTRAINTS table and
> > raise an Error if the new / updated row does not fit...
>
> I'd have a set of doesComply functions, the first two parameters
> as you have them but overload a set to support different datatypes
> specifically. Something like:
>
> CREATE FUNCTION doesComply(_rel TEXT, _key TEXT, _val INT) ...
> CREATE FUNCTION doesComply(_rel TEXT, _key TEXT, _val DATE) ...
> CREATE FUNCTION doesComply(_rel TEXT, _key TEXT, _val TEXT) ...
> CREATE FUNCTION doesComply(_rel TEXT, _key TEXT, _val NUMERIC) ...
>
> And then have a set of "attribute" tables (one for each datatype) to
> store the actual values in. At least PG can do some type checking for
> you that way. Either that, or just leave them all as text to text
> mappings in the database and only attempt to type things out in the
> client code.
>
> Not sure why you're doing the VALUES contortions as well, why not just:
>
> SELECT doesComply('relationname', 'col1', col2);
>
> ?
>
> --
> Sam http://samason.me.uk/
>
> --
> 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] generic modelling of data models; enforcing constraints dynamically...
On Thu, Sep 24, 2009 at 10:33:37PM +0200, InterRob wrote: > I came to think of another option: putting additional columns (that is: > addittional to the default set of fields) in xml, in a column that is part > of row (=object) it belongs to. > Any body has done so before? Any body has experience with XML schema > validation within PostgreSQL? Sorry; but was sounding a little over engineered before, it seems to be blowing out of proportion now. By whom and how are these (immensely complicated) rule sets going to be maintained? how is using XML going to make this any easier than using the tools native to the database? If they're validated inside the database then it's going to be done by a DB admin anyway, or am I missing something? If they're done by the DB admin, isn't it easy to just use the tools they're used to? -- Sam http://samason.me.uk/ -- 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] generic modelling of data models; enforcing constraints dynamically...
I guess it IS quite overengineered indeed... What I'm trying to do is to facilitate different fieldwork methodologies for archaeological research (on project basis); there is no final agreement on data structure and semantics; however, on a meta-level all choices are rational and can be modelled... Infact, all models can be related to each other: that's where the "hybrid" part comes in: I wish to implement the common denominator (90%) and then further extend this, enabing specific data model implementations -- including checks for data integrity. As soon as that works, it becomes possible to record changes at row-level -- providing access to data-snapshots in time. Furthermore, it becomes possible to build upon this central database automated tools for management and filing of information and different modes of data entry (including webbased)... The thing is: altering table structures (changes should be limited to adding columns) is required on a ad hoc basis and End User should be able to do so... I guess that requires some over engineering... ? Rob 2009/9/24 Sam Mason > On Thu, Sep 24, 2009 at 10:33:37PM +0200, InterRob wrote: > > I came to think of another option: putting additional columns (that is: > > addittional to the default set of fields) in xml, in a column that is > part > > of row (=object) it belongs to. > > Any body has done so before? Any body has experience with XML schema > > validation within PostgreSQL? > > Sorry; but was sounding a little over engineered before, it seems to be > blowing out of proportion now. By whom and how are these (immensely > complicated) rule sets going to be maintained? how is using XML going to > make this any easier than using the tools native to the database? > > If they're validated inside the database then it's going to be done by a > DB admin anyway, or am I missing something? If they're done by the DB > admin, isn't it easy to just use the tools they're used to? > > -- > Sam http://samason.me.uk/ > > -- > 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] v8.1 pgbench ERRORs
"Eric B. Ridge" writes: > Anyone seen this before? Yeah, you'll eventually overflow the balance fields because the deltas are always positive. More recent versions of pgbench use a positive-and-negative range for delta. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Can't find SRPMs for PG 8.1.18 on RHEL4
I'm having trouble finding the source RPMs for PostgreSQL 8.1.18 on RHEL4. I've tried looking in the following places with no luck (I can only find the regular RPMs). http://yum.pgsqlrpms.org/8.1/redhat/rhel-4-i386/ http://www.postgresql.org/ftp/binary/v8.1.18/linux/srpms/redhat/rhel-4-i386/ Any suggestions? -- Justin Pasher -- 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] v8.1 pgbench ERRORs
On Sep 24, 2009, at 5:15 PM, Tom Lane wrote: Yeah, you'll eventually overflow the balance fields because the deltas are always positive. More recent versions of pgbench use a positive-and-negative range for delta. Awesome, thanks! Was really worried that our new hardware was flaking out. eric -- 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] Can't find SRPMs for PG 8.1.18 on RHEL4
On Thu, 2009-09-24 at 15:43 -0500, Justin Pasher wrote: > I'm having trouble finding the source RPMs for PostgreSQL 8.1.18 on > RHEL4. I've tried looking in the following places with no luck (I can > only find the regular RPMs). > > http://yum.pgsqlrpms.org/8.1/redhat/rhel-4-i386/ ...because that URL is for binary packages. Please visit here: http://yum.pgsqlrpms.org/srpms/8.1/redhat/rhel-4-i386/repoview/postgresql.html -- Devrim GÜNDÜZ, RHCE Command Prompt - http://www.CommandPrompt.com devrim~gunduz.org, devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr http://www.gunduz.org signature.asc Description: This is a digitally signed message part
Re: [GENERAL] [HACKERS] libpq port number handling
Sam Mason writes: > Hum, why is PG doing an (unchecked) atoi on the user specified port > rather than leaving it up to getaddrinfo to resolve the port? It would > seem to require changing UNIXSOCK_PATH to accept a string as the "port > number", which is probably a bit much of a change. > The included doesn't feel very nice, but is probably more acceptable. I had been thinking about applying strstr to insist that the string contain only digits (and maybe spaces), but the range check you suggest is probably more useful. Anyone have objections? (BTW, are port numbers still limited to 16 bits in IPv6? If not then this won't do.) regards, tom lane -- 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] [HACKERS] libpq port number handling
On Thu, Sep 24, 2009 at 8:36 PM, Tom Lane wrote: > BTW, are port numbers still limited to 16 bits in IPv6? Yes. ...Robert -- 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] [HACKERS] libpq port number handling
Sam Mason writes: > + if (portnum < 1 || portnum > 65535) BTW, it strikes me that we could tighten this even more by rejecting target ports below 1024. This is guaranteed safe on all Unix systems I know of, because privileged ports can only be listened to by root-owned processes and we know the postmaster won't be one. I am not sure whether it would be possible to start the postmaster on a low-numbered port on Windows though. Anyone know? Even if it's possible, do we want to allow it? regards, tom lane -- 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] [HACKERS] libpq port number handling
Tom Lane wrote: > Sam Mason writes: >> +if (portnum < 1 || portnum > 65535) > > BTW, it strikes me that we could tighten this even more by rejecting > target ports below 1024. This is guaranteed safe on all Unix systems > I know of, because privileged ports can only be listened to by root-owned > processes and we know the postmaster won't be one. This is just an aside. The recent Linux system allows to assign a part of root privileges (called as capabilities) on a certain process. Example) # setcap cap_net_bind_service=ep /usr/local/pgsql/bin/postgres <-- it allows anyone to launch postmaster with cap_net_bind_service capability. $ pg_ctl -o "-i -p 100" start $ psql postgres -p 100 psql (8.5devel) Type "help" for help. postgres=# > Even if it's possible, do we want to allow it? I cannot find any merits. Thanks, -- OSS Platform Development Division, NEC KaiGai Kohei -- 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] [HACKERS] libpq port number handling
On Thu, 24 Sep 2009, Tom Lane wrote: Sam Mason writes: + if (portnum < 1 || portnum > 65535) BTW, it strikes me that we could tighten this even more by rejecting target ports below 1024. Restricting the target port seems like a bad idea. What about a firewall (or ssh tunnel) that did port forwarding. What PG binds to and what a client connects to may not be the same thing. Kris Jurka -- 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] [HACKERS] libpq port number handling
On Thu, Sep 24, 2009 at 8:59 PM, Tom Lane wrote: > Sam Mason writes: >> + if (portnum < 1 || portnum > 65535) > > BTW, it strikes me that we could tighten this even more by rejecting > target ports below 1024. This is guaranteed safe on all Unix systems > I know of, because privileged ports can only be listened to by root-owned > processes and we know the postmaster won't be one. I am not sure > whether it would be possible to start the postmaster on a low-numbered > port on Windows though. Anyone know? Even if it's possible, do we > want to allow it? I don't think we get much benefit out of artificially limiting libpq in this way. In 99.99% of cases it won't matter, and in the other 0.01% it will be a needless annoyance. I think we should restrict ourselves to checking what is legal, not what we think is a good idea. ...Robert -- 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] Looking for way to replicate master db to multiple mobile databases
On Thu, Sep 24, 2009 at 8:09 AM, Cédric Villemain wrote: > Le jeudi 24 septembre 2009, Selena Deckelmann a écrit : >> Hi! >> >> On Wed, Sep 23, 2009 at 10:11 AM, Bryan Montgomery > wrote: >> > Hi, >> > I'm looking for a way to replicate am master database to multiple (100+) >> > databases that are taken in to the field. Currently for each laptop we >> > dump and load the tables. However,there is only a small percentage of >> > data that changes on a frequent basis. >> > >> > I've been looking around and come across pyerplica, londiste and bucardo >> > - the documentation on most of these is fairly sparse. It seems that >> > Bucardo may be the best bet - at least initially. >> >> Bucardo is a good choice for this usage model because it was >> originally designed to work over a lossy network connections. > > yes, but isn't bucardo designed to 2 nodes only ? No, definitely not! You can replicate to any number of systems. And you can group them in whatever groups you'd like. Multi-master (as Joshua said) only works between two nodes, but master->slave can be from a master, to any number of slaves. -- http://chesnok.com/daily - me http://endpoint.com - work -- 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] Looking for way to replicate master db to multiple mobile databases
On 25/09/2009, at 12:50 PM, Selena Deckelmann wrote: On Thu, Sep 24, 2009 at 8:09 AM, Cédric Villemain wrote: Le jeudi 24 septembre 2009, Selena Deckelmann a écrit : Hi! On Wed, Sep 23, 2009 at 10:11 AM, Bryan Montgomery > wrote: Hi, I'm looking for a way to replicate am master database to multiple (100+) databases that are taken in to the field. Currently for each laptop we dump and load the tables. However,there is only a small percentage of data that changes on a frequent basis. I've been looking around and come across pyerplica, londiste and bucardo - the documentation on most of these is fairly sparse. It seems that Bucardo may be the best bet - at least initially. Bucardo is a good choice for this usage model because it was originally designed to work over a lossy network connections. yes, but isn't bucardo designed to 2 nodes only ? No, definitely not! You can replicate to any number of systems. And you can group them in whatever groups you'd like. Multi-master (as Joshua said) only works between two nodes, but master->slave can be from a master, to any number of slaves. I use bucardo extensively across multiple sites and with complex replication requirements. It does a great job. regards Grant -- 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] What is the difference of foreign key?
sheepjxx wrote: To be precise, The difference between create table a ( foreign key (id) referenced by b(name), ); create table b( id integer referenced by b(name), ); They're just two different forms of invalid SQL. The difference between them makes no difference. -- Lew -- 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] [HACKERS] libpq port number handling
On 25 sep 2009, at 02.59, Tom Lane wrote: Sam Mason writes: +if (portnum < 1 || portnum > 65535) BTW, it strikes me that we could tighten this even more by rejecting target ports below 1024. This is guaranteed safe on all Unix systems I know of, because privileged ports can only be listened to by root- owned processes and we know the postmaster won't be one. I am not sure whether it would be possible to start the postmaster on a low-numbered port on Windows though. Anyone know? Even if it's possible, do we want to allow it? Windows doesn't care. A non privileged process can open any port, both above and below 1024. Other than that, I agree with previous comments - restricting this in libpq won't actually help anything, but in a few limited cases it will be very annoying. /Magnus -- 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] [HACKERS] libpq port number handling
On Thu, 2009-09-24 at 20:36 -0400, Tom Lane wrote: > BTW, are port numbers still limited to 16 bits in IPv6? Port numbers are in TCP, not in IP. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
