Re: [GENERAL] Feature proposal, DBURL: psql pgsql://joe:p4zzw...@example.org:2345/dbname

[Adrian Klaver]

On 07/05/2017 08:31 AM, Hans Schou wrote:
2017-07-05 15:41 GMT+02:00 Adrian Klaver >:




[scheme[<+>dsn]]://[[username[:[password]]@][host][:port][/[dbname][/[[table[/[column[,column...]*]]]|sql]]]
^

The thing is that in a quick search on this I did not find a
reference implementation of this to compare against.


"dsn" is only relevant when the scheme is ODBC.


That was formatting error on my part, I was trying to point to the back 
half of the URI. The part with the table/column/sql sections.




In a situation where an application (like Drupal) get connect string 
(URI) it should be able to find the right driver to use. In case with of 
ODBC, a dsn (Data Source Name) is needed.


./hans




--
Adrian Klaver
adrian.kla...@aklaver.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] Feature proposal, DBURL: psql pgsql://joe:p4zzw...@example.org:2345/dbname

[Hans Schou]

2017-07-05 15:41 GMT+02:00 Adrian Klaver :

>
> [scheme[<+>dsn]]://[[username[:[password]]@][host][:port][/[
> dbname][/[[table[/[column[,column...]*]]]|sql]]]
> ^
>
> The thing is that in a quick search on this I did not find a reference
> implementation of this to compare against.
>

"dsn" is only relevant when the scheme is ODBC.

In a situation where an application (like Drupal) get connect string (URI)
it should be able to find the right driver to use. In case with of ODBC, a
dsn (Data Source Name) is needed.

./hans

Re: [GENERAL] Feature proposal, DBURL: psql pgsql://joe:p4zzw...@example.org:2345/dbname

[Hans Schou]

2017-07-05 15:15 GMT+02:00 Albe Laurenz :

>
> Unless I misunderstand, this has been in PostgreSQL since 9.2:
>

Sorry! I did not read the *new* manual.
(OK, 9.2 is not that new)

It is even mentioned in the man page.

Then I have a new proposal. Write a note about in
  psql --help

./hans

Re: [GENERAL] Feature proposal, DBURL: psql pgsql://joe:p4zzw...@example.org:2345/dbname

[Adrian Klaver]

On 07/05/2017 06:15 AM, Albe Laurenz wrote:

Hans Schou wrote:

The dburl (or dburi) has become common to use by many systems connecting to a 
database.
The feature is that one can pass all parameters in a string, which has similar 
pattern as
http-URI do.

Especially when using psql in a script, having the credentials in one string is
convenient.


The syntax could be:

[scheme[<+>dsn]]://[[username[:[password]]@][host][:port][/[dbname][/[[table[/[column[,col
umn...]*]]]|sql]]]


Example of usage:
   psql pgsql://joe:p4zzw...@example.org:2345/dbname


[...]


I have attached an example of how it could be implemented. It uses libpcre 
RegEx to pass
the dburl.


Unless I misunderstand, this has been in PostgreSQL since 9.2:


T think the OP was referring to the latter part of:

[scheme[<+>dsn]]://[[username[:[password]]@][host][:port][/[dbname][/[[table[/[column[,column...]*]]]|sql]]]
^

The thing is that in a quick search on this I did not find a reference 
implementation of this to compare against.




Yours,
Laurenz Albe




--
Adrian Klaver
adrian.kla...@aklaver.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] Feature proposal, DBURL: psql pgsql://joe:p4zzw...@example.org:2345/dbname

[Albe Laurenz]

Hans Schou wrote:
> The dburl (or dburi) has become common to use by many systems connecting to a 
> database.
> The feature is that one can pass all parameters in a string, which has 
> similar pattern as
> http-URI do.
> 
> Especially when using psql in a script, having the credentials in one string 
> is
> convenient.
> 
> 
> The syntax could be:
> 
> [scheme[<+>dsn]]://[[username[:[password]]@][host][:port][/[dbname][/[[table[/[column[,col
> umn...]*]]]|sql]]]
> 
> 
> Example of usage:
>   psql pgsql://joe:p4zzw...@example.org:2345/dbname

[...]

> I have attached an example of how it could be implemented. It uses libpcre 
> RegEx to pass
> the dburl.

Unless I misunderstand, this has been in PostgreSQL since 9.2:

https://www.postgresql.org/docs/current/static/libpq-connect.html#AEN45571
https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=b035cb9db7aa7c0f28581b23feb10d3c559701f6

Yours,
Laurenz Albe

-- 
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] Feature proposal, DBURL: psql pgsql://joe:p4zzw...@example.org:2345/dbname

[Christoph Moench-Tegeder]

## Hans Schou (hans.sc...@gmail.com):

> Example of usage:
>   psql pgsql://joe:p4zzw...@example.org:2345/dbname

Make the scheme "postgresql" and you're here:
https://www.postgresql.org/docs/current/static/libpq-connect.html#LIBPQ-CONNSTRING
"32.1.1.2. Connection URIs".

Regards,
Christoph

-- 
Spare Space


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

[GENERAL] Feature proposal, DBURL: psql pgsql://joe:p4zzw...@example.org:2345/dbname

[Hans Schou]

Hi

The dburl (or dburi) has become common to use by many systems connecting to
a database. The feature is that one can pass all parameters in a string,
which has similar pattern as http-URI do.
Especially when using psql in a script, having the credentials in one
string is convenient.

The syntax could be:

[scheme[<+>dsn]]://[[username[:[password]]@][host][:port][/[dbname][/[[table[/[column[,column...]*]]]|sql]]]

Example of usage:
  psql pgsql://joe:p4zzw...@example.org:2345/dbname

Where
  Scheme: pgsql
  Username: joe
  Password: p4zzw0rd
  Host: example.org
  Port: 2345
  Database: dbname

I have attached an example of how it could be implemented. It uses libpcre
RegEx to pass the dburl.

best regards
Hans
diff -Naur /home/hsc/tmp/postgresql-10beta1/src/bin/psql/dburl.c ./dburl.c
--- /home/hsc/tmp/postgresql-10beta1/src/bin/psql/dburl.c   1970-01-01 
01:00:00.0 +0100
+++ ./dburl.c   2017-07-05 13:52:30.823234720 +0200
@@ -0,0 +1,261 @@
+/*
+ * Compile:
+ *   gcc -Wall -DUNIT_TEST dburl.c -lpcre -o dburl
+ *
+ * Test:
+ *   ./dburl 'pgsqls://example/' 
'pgsqls://username:password@host:5432/dbname/SELECT * FROM mytable'
+ *   ./dburl 'mysql://username:password@host:3306/dbname/table/column1,column2'
+ *   ./dburl 'odbc+dsn:table/column1,column2'
+ */
+
+//#define INCLUDE_COMMENTS 1
+
+#ifdef UNIT_TEST
+#include 
+#endif
+
+#include 
+#include 
+#include 
+#include 
+#include 
+#include "dburl.h"
+
+#define OVECCOUNT (50*3)
+
+#define IDX_SCHEME   1
+#define IDX_DSN  IDX_SCHEME+1
+#define IDX_USERNAME IDX_DSN+1
+#define IDX_PASSWORD IDX_USERNAME+1
+#define IDX_HOST IDX_PASSWORD+1
+#define IDX_PORT IDX_HOST+1
+#define IDX_DBNAME   IDX_PORT+1
+#define IDX_TABLEIDX_DBNAME+1
+#define IDX_COLUMN   IDX_TABLE+1
+#define IDX_SQL  IDX_COLUMN+1
+
+const char *schemeitems[] = {
+   "null",
+   "scheme",
+   "dsn",
+   "username",
+   "password",
+   "host",
+   "port",
+   "dbname",
+   "table",
+   "column",
+   "sql"
+};
+
+#ifdef INCLUDE_COMMENTS
+#define cm(msg) "(?#\n " msg "\n)"
+#else
+#define cm(msg)
+#endif
+
+const char syntaxdescription[] =
+"[sql:][scheme[<+>dsn]]://[[username[:[password]]@][host][:port][/[dbname][/[[table[/[column[,column...]*]]]|sql]]]"
+;
+const char dburlregularexpression[] =
+"^"
+   cm("Optional prefix 'sql:'")
+   "(?:sql:)?"
+   cm("Scheme: pgsql")
+   "([-.a-z0-9]*)(?:[+]([-.a-z0-9]*))?"
+   cm("Required: URL identifier")
+   "://"
+   cm("Username + password")
+   "(?:"
+   cm("Username")
+   "([-a-z0-9_]+)"
+   cm("Password")
+   "(?::([^@]*))?@"
+   ")?"
+   cm("Hostname")
+   "("
+   cm("localhost | example")
+   "(?:[a-z0-9]+(?:-+[-a-z0-9]+)*)"
+   "|"
+   cm("Domain name with dot: example.com")
+   "(?:(?:[a-z0-9]+(?:-+[a-z0-9]+)*\\.)?"
+   "(?:[a-z0-9]+(?:-+[a-z0-9]+)*\\.)+(?:[a-z]{2,7})\\.?)"
+   "|"
+   cm("IPv4 number")
+   
"(?:(?:25[0-5]|2[0-4][0-9]|[0-1][0-9]{2}|[0-9][0-9]|[0-9])\\.){3}"
+   "(?:25[0-5]|2[0-4][0-9]|[0-1][0-9]{2}|[0-9][0-9]|[0-9])"
+   ")?"
+   cm("Port number: 3306|5432")
+   "(?::(\\d{1,5}))?"
+   cm("DB, table, SQL")
+   "(?:/"
+   "(?:"
+   cm("Dbname: joe|mydb, default $USER")
+   "(?:([_a-z0-9]+)?"
+   "(?:/"
+   "(?:"
+   cm("Table: mytable")
+   "(?:([_a-z0-9]+)"
+   cm("Columns: id, name")
+   "(?:/"
+   
"((?:[_a-z0-9]+)"
+   
"(?:,[_a-z0-9]+)*"
+   ")?"
+   ")?"
+   ")|("
+   cm("SQL: SELECT id, 
name FROM mytable")
+   "[^\\h]+\\h.+"
+   ")?"
+   ")?"
+   ")?"
+   ")?"
+   ")?"
+   ")?"
+"$"
+;
+
+static char *termstring(char *txt, int *ov, int idx, char *para_def) {
+   char *tmp = NULL;
+
+   /* if there is a match on this index... */
+   if (ov[2*idx+1] > 0) {
+   int length = ov[2*idx+1] - ov[2*idx];
+   if ((tmp = malloc(length+1))) {
+   strncpy(tmp, &txt[ov[2*idx]], length);
+   tmp[length] = 0;
+   }
+   }
+   re

Re: [GENERAL] Feature proposal and discussion: full-fledged column/function equivalence

[Vik Fearing]

On 08/01/2014 06:28 PM, Vik Fearing wrote:
>> So with all this in mind, is there any reason why we can't or shouldn't
>> > allow:
>> > 
>> > CREATE testfunction(test) returns int language sql as $$ select 1; $$;
>> > SELECT testfunction FROM test;
>> > 
>> > That would allow first-class calculated columns.
>> > 
>> > I assume the work is mostly at the parser/grammatical level.  Is there
>> > any reason why supporting that would be a bad idea?
> This is already supported since forever.
> 
> SELECT test.testfunction FROM test;
> 
> This link might be of interest to you:
> http://momjian.us/main/blogs/pgblog/2013.html#April_10_2013

Sorry, that's not the link I wanted.  This one is:
http://momjian.us/main/blogs/pgblog/2013.html#April_1_2013

Despite being posted on April 1st, it is not a joke. :)
-- 
Vik


-- 
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] Feature proposal and discussion: full-fledged column/function equivalence

[Vik Fearing]

On 08/01/2014 04:57 PM, Chris Travers wrote:
> Hi all;
> 
> I had a pleasant surprise today when demonstrating a previous misfeature
> in PostgreSQL behaved unexpectedly.  In further investigation, there is
> a really interesting syntax which is very helpful for some things I had
> not known about.
> 
> Consider the following:
> 
> CREATE TABLE keyvaltest (
> key text primary key,
> value text not null
> );
> INSERT INTO keyvaltest VALUES ('foo', 'bar'), ('fooprime', 'barprime');
> SELECT value(k) from keyvaltest k;
> 
> The latter performs exactly like 
> 
> SELECT k.value from keyvaltest k;

Interesting.  I wasn't aware of that.

> So the column/function equivalent is there.  This is probably not the
> best for production SQL code just because it is non-standard, but it is
> great for theoretical examples because it shows the functional
> dependency between tuple and tuple member.
> 
> It gets better:
> 
> CREATE OR REPLACE FUNCTION value(test) returns int language sql as $$
> select 3; $$;
> ERROR:  "value" is  already an attribute of type test
> 
> So this further suggests that value(test) is effectively an implicit
> function of test (because it is a trivial functional dependency).
> 
> So with all this in mind, is there any reason why we can't or shouldn't
> allow:
> 
> CREATE testfunction(test) returns int language sql as $$ select 1; $$;
> SELECT testfunction FROM test;
> 
> That would allow first-class calculated columns.
> 
> I assume the work is mostly at the parser/grammatical level.  Is there
> any reason why supporting that would be a bad idea?

This is already supported since forever.

SELECT test.testfunction FROM test;

This link might be of interest to you:
http://momjian.us/main/blogs/pgblog/2013.html#April_10_2013
-- 
Vik


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

[GENERAL] Feature proposal and discussion: full-fledged column/function equivalence

[Chris Travers]

Hi all;

I had a pleasant surprise today when demonstrating a previous misfeature in
PostgreSQL behaved unexpectedly.  In further investigation, there is a
really interesting syntax which is very helpful for some things I had not
known about.

Consider the following:

CREATE TABLE keyvaltest (
key text primary key,
value text not null
);
INSERT INTO keyvaltest VALUES ('foo', 'bar'), ('fooprime', 'barprime');
SELECT value(k) from keyvaltest k;

The latter performs exactly like

SELECT k.value from keyvaltest k;

So the column/function equivalent is there.  This is probably not the best
for production SQL code just because it is non-standard, but it is great
for theoretical examples because it shows the functional dependency between
tuple and tuple member.

It gets better:

CREATE OR REPLACE FUNCTION value(test) returns int language sql as $$
select 3; $$;
ERROR:  "value" is  already an attribute of type test

So this further suggests that value(test) is effectively an implicit
function of test (because it is a trivial functional dependency).

So with all this in mind, is there any reason why we can't or shouldn't
allow:

CREATE testfunction(test) returns int language sql as $$ select 1; $$;
SELECT testfunction FROM test;

That would allow first-class calculated columns.

I assume the work is mostly at the parser/grammatical level.  Is there any
reason why supporting that would be a bad idea?
-- 
Best Wishes,
Chris Travers

Efficito:  Hosted Accounting and ERP.  Robust and Flexible.  No vendor
lock-in.
http://www.efficito.com/learn_more

Re: [GENERAL] Feature Proposal: Constant Values in Columns or Foreign Keys

[Nils Gösche]

Bartosz Dmytrak wrote:

> according to DB theory:
> 1NF: Table faithfully represents a relation and has no repeating groups
> 2NF: No non-prime attribute in the table is functionally dependent on a 
> proper subset of anycandidate key.
> source: http://en.wikipedia.org/wiki/Database_normalization#Normal_forms

> so these constants are not in line with this approach.

This is true.  That's why you would have to guard the "constantness" of the 
column/attribute with a CHECK constraint, to avoid update anomalies.

Yes, the whole model would be simpler and more beautiful without the type 
column, and trigger functions on the derived tables instead. On the other hand, 
the foreign key including the type field might be faster than the foreign key 
without the type field plus trigger function. So, if this approach is any good, 
then only if it is actually faster. No, I haven't timed it :-)

Regards,
-- 
Nils Gösche
Don't ask for whom the  tolls.



smime.p7s
Description: S/MIME cryptographic signature

Re: [GENERAL] Feature Proposal: Constant Values in Columns or Foreign Keys

[Bartosz Dmytrak]

Hi,
according to DB theory:
*1NF: Table faithfully represents a relation and has no repeating groups*
*2NF: No non-prime attribute in the table is functionally dependent on a proper
subset of anycandidate key.*
source: http://en.wikipedia.org/wiki/Database_normalization#Normal_forms

so these constants are not in line with this approach.
You can implement one to one relation:

CREATE TABLE "tblBase"(
  id text NOT NULL,
  "SomeData" integer,
  CONSTRAINT "tblBase_pkey" PRIMARY KEY (id )
);

CREATE TABLE "tblDerived1"(
  id text NOT NULL,
  "Data1" integer,
  CONSTRAINT "tblDerived1_pkey" PRIMARY KEY (id ),
  CONSTRAINT "tblDerived1_id_fkey" FOREIGN KEY (id)
  REFERENCES "tblBase" (id) MATCH FULL
  ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED
);

CREATE TABLE "tblDerived2"(
  id text NOT NULL,
  "Data1" text,
  CONSTRAINT "tblDerived2_pkey" PRIMARY KEY (id ),
  CONSTRAINT "tblDerived2_id_fkey" FOREIGN KEY (id)
  REFERENCES "tblBase" (id) MATCH FULL
  ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED
);
*
*
So, You don't have any duplicated PK, every class is described
in separate entity (table).
If Your business logic needs to recognize classes in other way You can use
views, with constant field which is not stored on disk:

CREATE VIEW "vDerived1"
AS
SELECT *, 'DERIVED1'::text as "ClassType" FROM
"tblBase" NATURAL JOIN "tblDerived1";

CREATE VIEW "vDerived2"
AS
SELECT *, 'DERIVED2'::text as "ClassType" FROM
"tblBase" NATURAL JOIN "tblDerived2";
*
*
The problem is that "tblDerived1".id is not guaranteed to be not present in
"tblDerived2". This could be handled e.g. by trigger (before update) on
both tables (cross check), or using a kind of "middleware", I mean a
function which is responsible do perform inserts.

In my opinion it should be possible to recognize proper class based on
its attributes, so it should be quite easy to implement this function.
Function could be overloaded (same name, different set of attributes).

Regards,
Bartek

Re: [GENERAL] Feature Proposal: Constant Values in Columns or Foreign Keys

[Nils Gösche]

Vincent Veyron wrote:

> use a trigger on each of the derived tables, that cancels any insert if
> the same id already exists in the other table?

Yes, that would work.

> You don't say how your data gets inserted, but considering how
> complicated your preferred option looks, I have to ask why you can't
> use something as simple as :

[...]

> You'll have to build the queries according to the value of type, but
> this should give you the features you mention?

Sure, this is what I meant when I said that you could simply omit the type
field. However, if you still want the guarantee about not having two derived
rows for the same base row, you'll have to use a trigger function. The whole
point of the exercise is to avoid that trigger function and replace it with
a simple foreign key :-).

It's ok, maybe it's not worth the trouble.

Regards,
-- 
Nils Gösche
"Don't ask for whom the  tolls."



-- 
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] Feature Proposal: Constant Values in Columns or Foreign Keys

[Vincent Veyron]

Le mercredi 18 avril 2012 à 00:06 +0200, Nils Gösche a écrit :
> Bartosz Dmytrak wrote:
> 
> >
> The reason I like this particular way of modeling the data is that I have a 
> guarantee that there won't be an entry in both derived tables at the same 
> time for the same row in the base table; also, I can have further constraints 
> and foreign keys from and to the base table.
> 

use a trigger on each of the derived tables, that cancels any insert if
the same id already exists in the other table?

> Of course, I could also omit the type field and simply live with the 
> possibility of having two rows in the derived tables referring to the same 
> row of the base table. But it would be nice if I could rule that out with 
> simple constraints.

You don't say how your data gets inserted, but considering how
complicated your preferred option looks, I have to ask why you can't use
something as simple as :

CREATE TABLE base (
id int PRIMARY KEY,
some_data int NOT NULL,
type integer NOT NULL DEFAULT 1 
);

-- type 1 = derived1, type 2 = derived2

CREATE TABLE derived1 (
   id int PRIMARY KEY,
   data1 int NOT NULL,
   FOREIGN KEY (id) REFERENCES base (id) ON DELETE CASCADE
);

CREATE TABLE derived2 (
id int PRIMARY KEY,
data2 text NOT NULL,
   FOREIGN KEY (id) REFERENCES base (id) ON DELETE CASCADE
);


You'll have to build the queries according to the value of type, but
this should give you the features you mention?

-- 
Vincent Veyron
http://marica.fr/
Logiciel de gestion des sinistres assurances et des dossiers contentieux pour 
le service juridique


-- 
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] Feature Proposal: Constant Values in Columns or Foreign Keys

[Nils Gösche]

Tom Lane wrote:

> =?UTF-8?Q?Nils_G=C3=B6sche?=  writes:

> > I was quite surprised to find that this wasn't possible. Is there any
> > good reason why not?
> 
> It's contrary to SQL standard is why not.  And it's not just a matter
> of
> being outside the spec, as inheritance is; this is messing with the
> details of something that is defined in the standard.  As an example,
> I would wonder how such an FK is supposed to be represented in the
> spec-defined information schema views.  Other interesting questions
> include what would happen to the supposed constant during actions such
> as ON DELETE SET NULL or ON UPDATE CASCADE, which normally would result
> in a change in the referencing row.

Well, something I can have already is a column together with a NOT NULL 
constraint, and a CHECK constraint that ensures that the value is really 
constant, and a foreign key that includes this constant column. You could just 
handle it the same way.

The information schema is a good point, though. If the only way to put this 
into the information schema is to introduce some dummy column, possibly with 
NULL as a name or something, then I don't like the idea much myself anymore.

I guess the best I could hope for, then, is an optimization that does not store 
constant columns on disk ;-)

Regards,
-- 
Nils Gösche
"Don't ask for whom the  tolls."



-- 
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] Feature Proposal: Constant Values in Columns or Foreign Keys

[Tom Lane]

=?UTF-8?Q?Nils_G=C3=B6sche?=  writes:
> Bartosz Dmytrak wrote:
>> how about inheritance in postgres?

> I know about Postgres' inheritance feature, but would prefer a more standard 
> relational solution.

[ blink... ] That seems like a pretty silly argument for proposing
something that is *more* nonstandard.

> I was quite surprised to find that this wasn't possible. Is there any good 
> reason why not?

It's contrary to SQL standard is why not.  And it's not just a matter of
being outside the spec, as inheritance is; this is messing with the
details of something that is defined in the standard.  As an example,
I would wonder how such an FK is supposed to be represented in the
spec-defined information schema views.  Other interesting questions
include what would happen to the supposed constant during actions such
as ON DELETE SET NULL or ON UPDATE CASCADE, which normally would result
in a change in the referencing row.

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] Feature Proposal: Constant Values in Columns or Foreign Keys

[Nils Gösche]

Bartosz Dmytrak wrote:

> how about inheritance in postgres?

I know about Postgres' inheritance feature, but would prefer a more standard 
relational solution.

> With this approach all IDs will use the same sequence so there will not be 
> duplicated PKs in inherited tables.

In my case, the primary keys are usually uuids, actually. It was just an 
example, anyway.

> This could be also modeled with "standard" SQL approach without redundant 
> information. Solution depends on requirements.

Well, it could be modeled in the way I described, if only I could use constant 
values in foreign keys:

  FOREIGN KEY (id, 'derived2') REFERENCES base (id, type) ON DELETE CASCADE

I was quite surprised to find that this wasn't possible. Is there any good 
reason why not?

The reason I like this particular way of modeling the data is that I have a 
guarantee that there won't be an entry in both derived tables at the same time 
for the same row in the base table; also, I can have further constraints and 
foreign keys from and to the base table.

Of course, I could also omit the type field and simply live with the 
possibility of having two rows in the derived tables referring to the same row 
of the base table. But it would be nice if I could rule that out with simple 
constraints.

Regards,
-- 
Nils Gösche
Don't ask for whom the  tolls.



smime.p7s
Description: S/MIME cryptographic signature

Re: [GENERAL] Feature Proposal: Constant Values in Columns or Foreign Keys

[Bartosz Dmytrak]

Hi,
how about inheritance in postgres?

CREATE TABLE "tblBase"
(
  id serial NOT NULL, -- serial type is my assumption.
  "SomeData" integer,
  CONSTRAINT "tblBase_pkey" PRIMARY KEY (id )
)
WITH (
  OIDS=FALSE
);



CREATE TABLE "tblDerived1"
(
-- Inherited from table "tblBase":  id integer NOT NULL DEFAULT
nextval('"tblBase_id_seq"'::regclass),
-- Inherited from table "tblBase":  "SomeData" integer,
  "Data1" integer,
  CONSTRAINT "tblDerived1_pkey" PRIMARY KEY (id )
)
INHERITS ("tblBase")
WITH (
  OIDS=FALSE
);

CREATE TABLE "tblDerived2"
(
-- Inherited from table "tblBase":  id integer NOT NULL DEFAULT
nextval('"tblBase_id_seq"'::regclass),
 -- Inherited from table "tblBase":  "SomeData" integer,
  "Data2" text,
  CONSTRAINT "tblDerived2_pkey" PRIMARY KEY (id )
)
INHERITS ("tblBase")
WITH (
  OIDS=FALSE
);

inheritance is described in doc here:
http://www.postgresql.org/docs/9.1/static/ddl-inherit.html

With this approach all IDs will use the same sequence so there will not
be duplicated PKs in inherited tables.

This could be also modeled with "standard" SQL approach
without redundant information. Solution depends on requirements.

Regards,
Bartek


2012/4/17 Nils Gösche 

> Hi!
>
> I have a little feature proposal. Let me try to explain the motivation
> behind it.
>
> Suppose our application has two types of objects, looking somewhat like
> this:
>
> abstract class Base
> {
>public int Id;
>public int SomeData;
> }
>
> class Derived1 : Base
> {
>public int Data1;
> }
>
> class Derived2 : Base
> {
>public string Data2;
> }
>
> There are many ways of modeling this in a relational database. I am
> interested in this one:
>
> CREATE TYPE derived_type AS ENUM ('derived1', 'derived2);
>
> CREATE TABLE base (
>id int PRIMARY KEY,
>some_data int NOT NULL,
>type derived_type NOT NULL
> );
>
> CREATE UNIQUE INDEX base_derived_type_index ON base (id, derived_type);
>
> CREATE TABLE derived1 (
>   id int PRIMARY KEY,
>   data1 int NOT NULL,
>   type derived_type NOT NULL CHECK (type = 'derived1'),
>   FOREIGN KEY (id, type) REFERENCES base (id, type) ON DELETE CASCADE
> )
>
> CREATE TABLE derived2 (
>id int PRIMARY KEY,
>data2 text NOT NULL,
>type derived_type NOT NULL CHECK (type = 'derived2'),
>FOREIGN KEY (id, type) REFERENCES base (id, type) ON DELETE CASCADE
> )
>
> Note that the type column in derived1 and derived2 ensures that there is at
> most one row in either derived1 or derived2 which refers to a given row in
> "base".
>
> This works fine, actually. What bugs me, though, is the redundant data in
> the type columns of derived1 and derived2. It would be nice if I could
> either declare the columns as constant (so the data wouldn't be stored on
> disk anymore), or (even better) use a constant value in the foreign keys,
> as
> in
>
>FOREIGN KEY (id, 'derived2') REFERENCES base (id, type) ON DELETE
> CASCADE
>
> In the latter case, I could omit the type column of derived1 and derived2
> altogether.
>
> I suspect that it wouldn't be terribly hard to implement this. What do you
> think? Wouldn't this be nice to have?
>
> Regards,
> --
> Nils Gösche
> "Don't ask for whom the  tolls."
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

[GENERAL] Feature Proposal: Constant Values in Columns or Foreign Keys

[Nils Gösche]

Hi!

I have a little feature proposal. Let me try to explain the motivation
behind it.

Suppose our application has two types of objects, looking somewhat like
this:

abstract class Base
{
public int Id;
public int SomeData;
}

class Derived1 : Base
{
public int Data1;
}

class Derived2 : Base
{
public string Data2;
}

There are many ways of modeling this in a relational database. I am
interested in this one:

CREATE TYPE derived_type AS ENUM ('derived1', 'derived2);

CREATE TABLE base (
id int PRIMARY KEY,
some_data int NOT NULL,
type derived_type NOT NULL
);

CREATE UNIQUE INDEX base_derived_type_index ON base (id, derived_type);

CREATE TABLE derived1 (
   id int PRIMARY KEY,
   data1 int NOT NULL,
   type derived_type NOT NULL CHECK (type = 'derived1'),
   FOREIGN KEY (id, type) REFERENCES base (id, type) ON DELETE CASCADE
)

CREATE TABLE derived2 (
id int PRIMARY KEY,
data2 text NOT NULL,
type derived_type NOT NULL CHECK (type = 'derived2'),
FOREIGN KEY (id, type) REFERENCES base (id, type) ON DELETE CASCADE
)

Note that the type column in derived1 and derived2 ensures that there is at
most one row in either derived1 or derived2 which refers to a given row in
"base".

This works fine, actually. What bugs me, though, is the redundant data in
the type columns of derived1 and derived2. It would be nice if I could
either declare the columns as constant (so the data wouldn't be stored on
disk anymore), or (even better) use a constant value in the foreign keys, as
in 

FOREIGN KEY (id, 'derived2') REFERENCES base (id, type) ON DELETE
CASCADE

In the latter case, I could omit the type column of derived1 and derived2
altogether.

I suspect that it wouldn't be terribly hard to implement this. What do you
think? Wouldn't this be nice to have?

Regards,
-- 
Nils Gösche
"Don't ask for whom the  tolls."



-- 
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] Feature proposal

[Wojciech Strzałka]

No I don't, but definitely will try tomorrow

> Excerpts from wstrzalka's message of jue ago 26 03:18:36 -0400 2010:

>> So after turning off fsync & synchronous_commit (which I can afford as
>> I'm populating database from scratch)
>> I've stucked at 43 minutes for the mentioned table. There is no PK,
>> constrains, indexes, ... - nothing except for data.

> Are you truncating the table in the same transaction that copies the
> data into it?  If you do that, an optimization to skip WAL fires getting
> you a nice performance boost.  You need to have WAL archiving turned off
> though.

> Also, if you do that, perhaps there's no point in turning off fsync and
> synch_commit because an fsync will be done only once when the copy is
> complete.




-- 
Pozdrowienia,
 Wojciech Strzałka


-- 
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] Feature proposal

[Alvaro Herrera]

Excerpts from wstrzalka's message of jue ago 26 03:18:36 -0400 2010:

> So after turning off fsync & synchronous_commit (which I can afford as
> I'm populating database from scratch)
> I've stucked at 43 minutes for the mentioned table. There is no PK,
> constrains, indexes, ... - nothing except for data.

Are you truncating the table in the same transaction that copies the
data into it?  If you do that, an optimization to skip WAL fires getting
you a nice performance boost.  You need to have WAL archiving turned off
though.

Also, if you do that, perhaps there's no point in turning off fsync and
synch_commit because an fsync will be done only once when the copy is
complete.

-- 
Álvaro Herrera 
The PostgreSQL Company - Command Prompt, Inc.
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] Feature proposal

[wstrzalka]

On 26 Aug, 01:28, pie...@hogranch.com (John R Pierce) wrote:
>   On 08/25/10 11:47 AM, Wojciech Strzałka wrote:
>
> >   The data set is 9mln rows - about 250 columns
>
> Having 250 columns in a single table sets off the 'normalization' alarm
> in my head.
>
> --
> Sent via pgsql-general mailing list (pgsql-gene...@postgresql.org)
> To make changes to your 
> subscription:http://www.postgresql.org/mailpref/pgsql-general

Yeap - but it is as it is.
I need to migrate PG first - then start thinking about schema changes

-- 
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] Feature proposal

[wstrzalka]

On 26 Sie, 08:06, wstrzalka  wrote:
> On 26 Aug, 01:28, pie...@hogranch.com (John R Pierce) wrote:
>
> >   On 08/25/10 11:47 AM, Wojciech Strzałka wrote:
>
> > >   The data set is 9mln rows - about 250 columns
>
> > Having 250 columns in a single table sets off the 'normalization' alarm
> > in my head.
>
> > --
> > Sent via pgsql-general mailing list (pgsql-gene...@postgresql.org)
> > To make changes to your 
> > subscription:http://www.postgresql.org/mailpref/pgsql-general
>
> Yeap - but it is as it is.
> I need to migrate PG first - then start thinking about schema changes


So after turning off fsync & synchronous_commit (which I can afford as
I'm populating database from scratch)
I've stucked at 43 minutes for the mentioned table. There is no PK,
constrains, indexes, ... - nothing except for data.

The behaviour changed - I'm utilizing the core 100%, iostat shows the
write peaks about 70MB/s, the table shown by \d+ is growing all the
time as it growth before.
Is there anything I can look at?
Anyway the load to PG is much faster then dump from the old database
and the current load time is acceptable for me.

-- 
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] Feature proposal

[Peter Eisentraut]

On ons, 2010-08-25 at 00:15 -0700, wstrzalka wrote:
> I'm currently playing with very large data import using COPY from
> file.
> 
> As this can be extremely long operation (hours in my case) the nice
> feature would be some option to show operation progress - how many
> rows were already imported.

A feature like this is being worked on:
https://commitfest.postgresql.org/action/patch_view?id=368


-- 
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] Feature proposal

[Adrian von Bidder]

Heyho!

On Wednesday 25 August 2010 09.15:33 wstrzalka wrote:
> I'm currently playing with very large data import using COPY from
> file.
> 
> As this can be extremely long operation (hours in my case) the nice
> feature would be some option to show operation progress - how many
> rows were already imported.

Recently, I've found (on Linux, don't know if other OSs export this 
information) /proc//fdinfo/ extremely helpful.  It tells you the 
position of the file pointer of file number  in process  (I guess 
for a COPY import this would be the postgresql backend handling your import 
session.)

Unlike other options, you can also use this if you only notice that the 
process is long-running after you've already started it.

Of course it probably will not work if the file is mmapped or otherwise not 
read in a sequential fashion.

cheers
-- vb

-- 
All Hail Discordia!


signature.asc
Description: This is a digitally signed message part.

Re: [GENERAL] Feature proposal

[Vick Khera]

On Wed, Aug 25, 2010 at 8:48 PM, Craig Ringer
 wrote:
> synchronous_commit also has effects on data safety. It permits the loss of
> transactions committed within the commit delay interval if the server
> crashes. If you turn it on, you need to decide how much recent work you can
> afford to lose if the database crashes. Not sure if it can be applied with a
> reload or whether it requires a full server restart.
>

I routinely set synchronous_commit = off  on a per-connection or
per-transaction basis.  The beauty of it is that it still honors
transaction boundaries.  That is, if there is a server crash the
transaction will be either there or not as a whole; it will not be
partially applied.  This works great for bulk imports and changes to
the DB for me, since I can always just re-run my programs on such
failure and everything will pick up where it left off.  It takes some
planning but is worth it.

> So: if you don't know exactly what you're doing, leave fsync alone.

I agree -- leave fsync alone. You get benefit from synchronous_commit
without the corruption risk.

The other advice on boosting checkpoint segments and timeout are spot
on.  Make them pretty big and it will make your import go way faster.
If you have a spare disk on which to move the checkpoint segments so
that you eliminate the seek time on them, move them to get even more
speed.  After your import, you can make the number of segments smaller
again if that suits your workload.

-- 
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] Feature proposal

[Sam Mason]

On Wed, Aug 25, 2010 at 08:47:10PM +0200, Wojciech Strzaaaka wrote:
>  The data set is 9mln rows - about 250 columns

250 columns sounds very strange to me as well! I start to getting
worried when I hit a tenth of that.

>  CPU utilization - 1,2% (half of the one core)
>  iostat shows writes ~6MB/s,  20% busy
>  when I run 2 loads in parallel the CPU is split to 2*0,6%, hdd write
>  ~7MB (almost the same)

If you've got indexes set up on the table then I'd expect this sort
of behavior, you could try dropping them before the copy and then
recreating them afterward.

It would be great if PG could do these sorts of bulk index updates
automatically!  Maybe run the first few tens/hundred changes in the
main index and then start logging the rows that will need indexing and
bulk process and merge them at the end.  Concurrent access seems a bit
more complicated, but shouldn't be too bad.  The case of a UNIQUE index
seems to require a change in behavior.  For example, the following are
executed concurrently:

  Client A: COPY foo (id) FROM stdin;
  Client B: INSERT INTO foo (id) VALUES (1);

with A starting before and finishing after B, and A sends a row with
id=1.

At the moment the behavior would be for A's data to be indexed
immediately and hence B's conflicting change would fail.  If PG did
bulk index merging at the end, this would change to B's succeeding and
A's failing when the index was brought up to date.  These semantics are
still compatible with SQL, just different from before so some code may
be (incorrectly) relying on this.

I've read discussions from:
  http://archives.postgresql.org/pgsql-hackers/2008-02/msg00811.php
and
  http://archives.postgresql.org/pgsql-general/2008-01/msg01048.php

but not found much recent.  It seems to hold together better than
the first suggestion.  Second post notes that you may be better off
working in work_mem batches to help preventing spilling to disk.  Sounds
reasonable, and if it's OK to assume the new rows will be physically
close to each other then they can be recorded as ranges/run length
encoded to reduce the chance of spilling to disk for even very large
inserts.  As per the second post, I'm struggling with BEFORE INSERT
triggers as well, their semantics seem to preclude most optimizations.

>  what's also interesting - table is empty when I start (by truncate)
>  but while the COPY is working, I see it grows (by \d+ or
>  pg_total_relation_size) about 1MB per second
>  what I'd expect it should grow at checkpoints only, not all the
>  time - am I wrong?

AFAIU, it'll constantly grow.

-- 
  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] Feature proposal

[Craig Ringer]

On 26/08/2010 1:06 AM, Steve Clark wrote:

On 08/25/2010 12:30 PM, Joshua D. Drake wrote:

On Wed, 2010-08-25 at 12:20 -0400, Eric Comeau wrote:



Without even changing any line of data or code in sql !

Incredible, isn't it ?



Curious- what postgresql.conf settings did you change to improve it?


The most obvious would be to turn fsync off, sychronous_commit off,
increase work_mem, increase checkpoint_timeout, increase wal_segments.

JD








can these be changed on the fly via set commands or does the config file
have to be changed and postgres stopped and restarted.


First: Many options can be changed by editing the config file then 
telling the postmaster to reload its configuration, rather that 
restarting the postmaster. See pg_ctl.


As for the specific options:

Checkpoint and WAL tuning is necessary and important in any real 
postgresql instance under load, and it's quite safe to adjust the 
checkpoint timeouts and wal segment counts to suit your needs. You'll 
need a restart to change the number of wal segments; I'm not so sure 
about the checkpoint timeout.


You can't change fsync without a config file edit and a restart. You 
should **NEVER** be using fsync=off with data you cannot afford to lose, 
so it's a good thing in a way. You might use it to help initially load a 
database with bulk data, but fsync should be turned back on and the 
database restarted before you start actually using it. fsync=off is 
**NOT SAFE**.


synchronous_commit also has effects on data safety. It permits the loss 
of transactions committed within the commit delay interval if the server 
crashes. If you turn it on, you need to decide how much recent work you 
can afford to lose if the database crashes. Not sure if it can be 
applied with a reload or whether it requires a full server restart.


So: if you don't know exactly what you're doing, leave fsync alone.

--
Craig Ringer

Tech-related writing at http://soapyfrogs.blogspot.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] Feature proposal

[John R Pierce]

 On 08/25/10 11:47 AM, Wojciech Strzałka wrote:

  The data set is 9mln rows - about 250 columns


Having 250 columns in a single table sets off the 'normalization' alarm 
in my head.







--
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] Feature proposal

[Wojciech Strzałka]

 Yea - I'll try to optimize as I had a plan to write to
 pgsql.performance for rescue anyway.

 I don't know exact hardware specification yet - known facts at the
 moment are:
 Sun Turgo?? (SPARC) with 32 cores
 17GB RAM (1GB for shared buffers)
 hdd - ?
 OS - Solaris 10 - the system is running in the zone (Solaris
 virtualization) - however during test nothing else is utilizing the
 machine.
 PostgreSQL 8.4.4 64bit
 
 The data set is 9mln rows - about 250 columns
 The result database size is ~9GB
 Load time ~2h 20min
 CPU utilization - 1,2% (half of the one core)
 iostat shows writes ~6MB/s,  20% busy
 when I run 2 loads in parallel the CPU is split to 2*0,6%, hdd write
 ~7MB (almost the same)

 postgresql.conf changes:
 checkpoint_segments - 128
 checkpoint_timeout - 30min
 shared_buffers - 1GB
 maintenance_work_mem - 128MB


 does it looks like my HDD is the problem? or maybe the Solaris
 virtualization?

 what's also interesting - table is empty when I start (by truncate)
 but while the COPY is working, I see it grows (by \d+ or
 pg_total_relation_size) about 1MB per second
 what I'd expect it should grow at checkpoints only, not all the
 time - am I wrong?

 
 

> On Wed, 2010-08-25 at 12:20 -0400, Eric Comeau wrote:
>>  
>> > Without even changing any line of data or code in sql !
>> > 
>> > Incredible, isn't it ?
>> > 
>> 
>> Curious- what postgresql.conf settings did you change to improve it?

> The most obvious would be to turn fsync off, sychronous_commit off,
> increase work_mem, increase checkpoint_timeout, increase wal_segments.

> JD

>> 
>> 
>> 




-- 
Pozdrowienia,
 Wojciech Strzałka


-- 
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] Feature proposal

[Steve Clark]

On 08/25/2010 12:30 PM, Joshua D. Drake wrote:

On Wed, 2010-08-25 at 12:20 -0400, Eric Comeau wrote:



Without even changing any line of data or code in sql !

Incredible, isn't it ?



Curious- what postgresql.conf settings did you change to improve it?


The most obvious would be to turn fsync off, sychronous_commit off,
increase work_mem, increase checkpoint_timeout, increase wal_segments.

JD








can these be changed on the fly via set commands or does the config file have 
to be
changed and postgres stopped and restarted.

postgres 8.3.7 on freebsd.

--
Stephen Clark
NetWolves
Sr. Software Engineer III
Phone: 813-579-3200
Fax: 813-882-0209
Email: steve.cl...@netwolves.com
www.netwolves.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] Feature proposal

[Joshua D. Drake]

On Wed, 2010-08-25 at 12:20 -0400, Eric Comeau wrote:
>  
> > Without even changing any line of data or code in sql !
> > 
> > Incredible, isn't it ?
> > 
> 
> Curious- what postgresql.conf settings did you change to improve it?

The most obvious would be to turn fsync off, sychronous_commit off,
increase work_mem, increase checkpoint_timeout, increase wal_segments.

JD

> 
> 
> 

-- 
PostgreSQL.org Major Contributor
Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579
Consulting, Training, Support, Custom Development, Engineering
http://twitter.com/cmdpromptinc | http://identi.ca/commandprompt


-- 
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] Feature proposal

[Eric Comeau]

On Wed, 2010-08-25 at 17:06 +0200, Denis BUCHER wrote:
> Le 25.08.2010 09:15, wstrzalka a crit :
> > I'm currently playing with very large data import using COPY from
> > file.
> >
> > As this can be extremely long operation (hours in my case) the nice
> > feature would be some option to show operation progress - how many
> > rows were already imported.
> >
> > Or maybe there is some way to do it? As long as postgres have no read-
> > uncommited I think I can estimate it only by destination table size ??
> 
> By the way, did you try to optimize your postgresql server ?
> 
> In my case I was able to reduce a big data update from :
> 1 hour 15 minutes
> to :
> 5 minutes
> 
> Without even changing any line of data or code in sql !
> 
> Incredible, isn't it ?
> 

Curious- what postgresql.conf settings did you change to improve it?



-- 
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] Feature proposal

[Denis BUCHER]

Le 25.08.2010 09:15, wstrzalka a écrit :

I'm currently playing with very large data import using COPY from
file.

As this can be extremely long operation (hours in my case) the nice
feature would be some option to show operation progress - how many
rows were already imported.

Or maybe there is some way to do it? As long as postgres have no read-
uncommited I think I can estimate it only by destination table size ??


By the way, did you try to optimize your postgresql server ?

In my case I was able to reduce a big data update from :
1 hour 15 minutes
to :
5 minutes

Without even changing any line of data or code in sql !

Incredible, isn't it ?

Denis

--
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] Feature proposal

[Wappler, Robert]

On 2010-08-25, wstrzalka wrote:
 
> I'm currently playing with very large data import using COPY from
file.
> 
> As this can be extremely long operation (hours in my case) the nice
> feature would be some option to show operation progress - how many
> rows were already imported.
> 
> Or maybe there is some way to do it? As long as postgres have no read-
> uncommited I think I can estimate it only by destination table size ??
> 
> -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To
> make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

Hi,
 
You can use tools like pv for a progress bar and pipe the output into
psql.

HTH
-- 
Robert...
 


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

[GENERAL] Feature proposal

[wstrzalka]

I'm currently playing with very large data import using COPY from
file.

As this can be extremely long operation (hours in my case) the nice
feature would be some option to show operation progress - how many
rows were already imported.

Or maybe there is some way to do it? As long as postgres have no read-
uncommited I think I can estimate it only by destination table size ??

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