[GENERAL] Customising pg directories
I am building a new server with a separate partition for data and xlogs. What is the correct way to do this? Can I create a symlink from /var/lib/pgsql/9.1/data -> /pg_data (and then a symlink in /pgdata for xlogs-> /pg_xlog) Or do I have to modify $PGDATA in the init script? This is all after installing packages but prior to 'service postgresql-91 initdb' cheers -- 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_min_duration_statement modification for non superusers?
Paul McGarry wrote: > I have a situation where I'd like to lower the > log_min_duration_statement for a particular connection > . > The DB is used for several reporting type queries which may reasonably > take several seconds so the log_min_duration_statement in the DB is > set accordingly. > > However there are critical paths in the app where queries are more > interactive and we'd like to log any statements using a much lower > limit. > > A superuser could, for example: > set log_min_duration_statement = 500; > when connecting to the DB but normal users can't. > > Is there a trick that would allow us to enable non-super users to > lower the log_min_duration_statement value? > > The only thing I have though of is connecting as a Super User and then > using "SET ROLE" to change to the non-priviledged user afterwards but > really we don't want our Super User authentication credentials on the > application server. You could write a SECURITY DEFINER function thusly: CREATE OR REPLACE FUNCTION set_log_min_duration(integer) RETURNS void LANGUAGE plpgsql VOLATILE STRICT SECURITY DEFINER SET search_path TO pg_catalog,pg_temp AS $$BEGIN EXECUTE 'SET log_min_duration_statement = ' || $1::text; END$$; REVOKE EXECUTE ON FUNCTION set_log_min_duration(integer) FROM PUBLIC; Then you can grant EXECUTE privileges to the users you want to be able to change the setting. 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] Customising pg directories
On Thu, Jun 28, 2012 at 1:20 PM, Ben Carbery wrote: > I am building a new server with a separate partition for data and > xlogs. What is the correct way to do this? > > Can I create a symlink from /var/lib/pgsql/9.1/data -> /pg_data (and > then a symlink in /pgdata for xlogs-> /pg_xlog) > Or do I have to modify $PGDATA in the init script? > > This is all after installing packages but prior to 'service > postgresql-91 initdb' > > cheers > > initdb --pgdata --xlogdir , is the command which suit's your requirement. This command will take care of creating symblinks as well. --- Regards, Raghavendra EnterpriseDB Corporation Blog: http://raghavt.blogspot.com/
[GENERAL] function lookup using a "real" function call
Hello,
Is it possible to find out the (list of) function oids from a function
call?
e.g. select MYFUNC('foo', 1234)
=> someting like
select * from get_function_oids($$MYFUNC('foo', 1234)$$)
best regards,
Marc Mamin
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] create a script which imports csv data
Hi,
I have to create a script which imports csv data into postgresql ...and have a
few questions about the best way to do it.
The csv data is automatically created from an external database so I have no
influence over which columns etc are downloaded.
The csv comes without an fid field and has therefore no unique identifier.
How can I best create a table for the import?
Would I first create a table without an fid and then after the import create a
sequence and add the sequence to the table, then somehow update the fid field?
could anyone show me the best way to do this?
at the moment I am doing this...which makes postgresql throw an error because
obviously the 'fid' field is missing from the csv data!
CREATE SEQUENCE fid_seq INCREMENT 1 START 1;
CREATE TABLE Anlagenregister_Aktuell_2011 (
fid INTEGER NOT NULL DEFAULT nextval('fid_seq'),
Firma TEXT,
Anlagenschluessel TEXT,
Anschrift TEXT,
PLZ TEXT,
Ort TEXT,
Bundesland TEXT,
Energietraeger TEXT,
Inbetriebnahmedatum DATE,
Netzzugangsdatum DATE,
Netzabgangsdatum DATE,
Ausserbetriebnahmedatum DATE,
Installierte_Leistung_kW numeric(11,4),
Lastgangmessung TEXT,
Einspeisemanagement TEXT,
Spannungsebene TEXT,
Zaehlpunktbezeichnung TEXT,
Anlagentyp TEXT,
Geographische_Koordinaten TEXT,
Schalloptimierung TEXT,
Biomasse_KWK_Bonus TEXT,
Biomasse_Technologie_Bonus TEXT,
PRIMARY KEY (fid)
);
copy Anlagenregister_Aktuell_2011 FROM
'/var/www/Anlagenregister_Aktuell_2011.csv' DELIMITERS ';' CSV;
thanks,
Rob
Re: [GENERAL] create a script which imports csv data
On 28/06/2012 12:53, Robert Buckley wrote:
> Hi,
>
> I have to create a script which imports csv data into postgresql ...and
> have a few questions about the best way to do it.
>
> The csv data is automatically created from an external database so I
> have no influence over which columns etc are downloaded.
>
> The csv comes without an fid field and has therefore no unique identifier.
>
> How can I best create a table for the import?
>
> Would I first create a table without an fid and then after the import
> create a sequence and add the sequence to the table, then somehow update
> the fid field?
>
> could anyone show me the best way to do this?
Yes, you can do that - create the table initially without a primary key,
import the data, then do something like this:
alter table Anlagenregister_Aktuell_2011 add column fid serial;
update Anlagenregister_Aktuell_2011 set fid =
nextval('Anlagenregister_Aktuell_2011_fid_seq');
As an aside, note that the upper-case letters in the table name get
folded automatically to lower--case unless you double-quote it:
"Anlagenregister_Aktuell_2011".
Ray.
--
Raymond O'Donnell :: Galway :: Ireland
r...@iol.ie
--
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] create a script which imports csv data
>
>
> From: pgsql-general-ow...@postgresql.org On Behalf Of Robert Buckley
> Sent: Donnerstag, 28. Juni 2012 13:53
> To: pgsql-general@postgresql.org
> Subject: [GENERAL] create a script which imports csv data
>
> Hi,
>
> I have to create a script which imports csv data into postgresql
...and have a few questions about the best way to do it.
>
> The csv data is automatically created from an external database so I
have no influence over which columns etc are downloaded.
>
> The csv comes without an fid field and has therefore no unique
identifier.
>
> How can I best create a table for the import?
>
> Would I first create a table without an fid and then after the import
create a sequence and add the sequence to the table, then somehow update
the fid field?
>
> could anyone show me the best way to do this?
>
>
> at the moment I am doing this...which makes postgresql throw an error
because obviously the 'fid' field is missing from the csv data!
>
> CREATE SEQUENCE fid_seq INCREMENT 1 START 1;
>
> CREATE TABLE Anlagenregister_Aktuell_2011 (
> fid INTEGER NOT NULL DEFAULT nextval('fid_seq'),
> Firma TEXT,
> Anlagenschluessel TEXT,
> Anschrift TEXT,
> PLZ TEXT,
> Ort TEXT,
> Bundesland TEXT,
> Energietraeger TEXT,
> Inbetriebnahmedatum DATE,
> Netzzugangsdatum DATE,
> Netzabgangsdatum DATE,
> Ausserbetriebnahmedatum DATE,
> Installierte_Leistung_kW numeric(11,4),
> Lastgangmessung TEXT,
> Einspeisemanagement TEXT,
> Spannungsebene TEXT,
> Zaehlpunktbezeichnung TEXT,
> Anlagentyp TEXT,
> Geographische_Koordinaten TEXT,
> Schalloptimierung TEXT,
> Biomasse_KWK_Bonus TEXT,
> Biomasse_Technologie_Bonus TEXT,
> PRIMARY KEY (fid)
> );
>
> copy Anlagenregister_Aktuell_2011 FROM
'/var/www/Anlagenregister_Aktuell_2011.csv' DELIMITERS ';' CSV;
Hello,
1st:
you don't need to create the sequence.
Just use
CREATE TABLE Anlagenregister_Aktuell_2011 (
fid SERIAL NOT NULL ,
...
with serial as data type, a sequence will be built for you in the
backgroud.
2nd:
in COPY you can list the the columns of the csv content:
COPY Anlagenregister_Aktuell_2011 (firma, anlagenschluessel, ...) FROM
As fid is not listed here, it will be filled by its default value
(sequence).
best regards,
Marc Mamin
--
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] create a script which imports csv data
Robert Buckley wrote: I have to create a script which imports csv data into postgresql The csv data is automatically created from an external database so I have no influence over which columns etc are downloaded. How can I best create a table for the import? This is what I do: 1) I have a table defined for import which reflects the CSV structure of the data to be imported, i.e., since I know what the fields are on each line of the CSV, this template table has columns defined to accommodate each known field. This table never actually gets data written to it. 2) To import data, my script creates a temporary import table LIKE the import template table. 3) The script then transfers and transform the data from the temporary import table to another permanent table that has the structure, including a primary key, that is more useful for my purposes. (It omits some of the columns which I do not really need from the CSV, uses a different name for one column, and adds some reference information. You could do calculations here as well.) 4) The temporary import table is deleted at the end of the import session. Here is a sanitized (names changed to protect the innocent) version of the script (the script parameter '$1' is the name of the CSV file): #!/bin/bash # This script imports a CSV file of transactions from Discover. #!/bin/bash # This script imports a CSV file of transactions from Discover. psql mydb <<-_END-OF-SCRIPT_ CREATE LOCAL TEMPORARY TABLE i (LIKE my_financial_schema.import_discover_card); COPY i (transaction_date, post_date, description, amount, category, share, net, type, paid_date) FROM '$1' WITH (FORMAT CSV, DELIMITER ',', QUOTE '"'); INSERT INTO my_financial_schema.my_permanent_record_table( transaction_date, paid_date, reference, category, amount, description ) SELECT transaction_date, paid_date, 'Discover Card', type, net, description FROM i; DROP TABLE i; _END-OF-SCRIPT_ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Postgresql 9.0.6 alway run VACUUM ANALYZE pg_catalog.pg_attribute
Hello I am using PostgreSQL 9.0.6 64 bit on Windows 2003 64bit. When i view Postgresql status, there are some autovaccum alway run. Ex VACUUM ANALYZE pg_catalog.pg_attribute. Is it problem of PostgreSQL? Please help me. Tuan Hoang Anh.
Re: [GENERAL] create database from template requires the source database to be unused
> -Original Message- > From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general- > It'd be really interesting to relax that limitation to "... other > non-read-only transaction ... " and have a database that's being cloned > block or reject > DML, UPDATE, etc. There are some issues with that though: > > (a) Transactions are read/write by default. Most apps don't bother to > SET TRANSACTION READ ONLY or BEGIN READ ONLY TRANSACTION . Most > non-read-only transactions will make no changes, but the database can't > know that until they complete. > > (b) AFAIK even truly read-only transactions can set hint bits and > certain other system level database metadata. > > (c) Because of (a) it'd be necessary to block your CREATE DATABASE ... > TEMPLATE ... until all transactions finished and sessions were idle, or > to abort all transactions and roll them back. I've read that postgres uses MVCC for transactions, and that it creates snapshots of the database for each transaction. Couldn't the create database command just use that snapshot? 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] create database from template requires the source database to be unused
> I've read that postgres uses MVCC for transactions, and that it creates > snapshots of the database for each transaction. Couldn't the create > database command just use that snapshot? Database creation cannot be done inside a transaction (one of the few DDL statements that can't), so no. -- If you can't see the forest for the trees, Cut the trees and you'll see there is no forest. -- 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] create a script which imports csv data
On Thu, Jun 28, 2012 at 2:12 PM, Berend Tober wrote: > Robert Buckley wrote: >> >> I have to create a script which imports csv data into postgresql >> The csv data is automatically created from an external database >> so I have no influence over which columns etc are downloaded. >> >> How can I best create a table for the import? >> > > This is what I do: > > 1) I have a table defined for import which reflects the CSV structure of the > data to be imported, i.e., since I know what the fields are on each line of > the CSV, this template table has columns defined to accommodate each known > field. This table never actually gets data written to it. > > 2) To import data, my script creates a temporary import table LIKE the > import template table. > > 3) The script then transfers and transform the data from the temporary > import table to another permanent table that has the structure, including a > primary key, that is more useful for my purposes. (It omits some of the > columns which I do not really need from the CSV, uses a different name for > one column, and adds some reference information. You could do calculations > here as well.) > > 4) The temporary import table is deleted at the end of the import session. > With the file_fdw you can skip some steps: http://www.postgresql.org/docs/9.1/static/file-fdw.html You can SELECT from the CSV file. -- 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] Customising pg directories
Thanks too... :) --Raghav On Thu, Jun 28, 2012 at 4:52 PM, Ben Carbery wrote: > Too easy, thanks. > > On 28 June 2012 18:38, Raghavendra > wrote: > > On Thu, Jun 28, 2012 at 1:20 PM, Ben Carbery > wrote: > >> > >> I am building a new server with a separate partition for data and > >> xlogs. What is the correct way to do this? > >> > >> Can I create a symlink from /var/lib/pgsql/9.1/data -> /pg_data (and > >> then a symlink in /pgdata for xlogs-> /pg_xlog) > >> Or do I have to modify $PGDATA in the init script? > >> > >> This is all after installing packages but prior to 'service > >> postgresql-91 initdb' > >> > >> cheers > >> > > > > initdb --pgdata --xlogdir , is the > command > > which suit's your requirement. > > > > This command will take care of creating symblinks as well. > > > > --- > > Regards, > > Raghavendra > > EnterpriseDB Corporation > > Blog: http://raghavt.blogspot.com/ > > >
Re: [GENERAL] create database from template requires the source database to be unused
Alban Hertroys writes: >> I've read that postgres uses MVCC for transactions, and that it creates >> snapshots of the database for each transaction. Couldn't the create >> database command just use that snapshot? > Database creation cannot be done inside a transaction (one of the few > DDL statements that can't), so no. It's a little more complicated than that. The real answer is that CREATE DATABASE works by doing a filesystem copy of the source database, so if there are any concurrent changes going on, it can't get a consistent snapshot of that database's state. It's interesting to think about ways that that restriction might be weakened, but I don't see any way to do it that wouldn't involve taking some type of lock on each table in the source database --- and, at some point, locking out the ability to create any new tables there too. That would be messy, deadlock-prone, and probably still pretty restrictive for transactions in the source database. Another issue, if the locks in question don't prohibit writes, is that as soon as you've cloned a given table any WAL-logged actions issued against that table would have to be duplicated for the clone in the new database; something the transactions in the source database couldn't be expected to know that they have to do, since after all the new database doesn't exist yet from their perspective. On the whole, even if it's possible at all, the work-to-payoff ratio doesn't look very attractive. 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] Stored function signature incompatibility in index (probably a bug)
Hi, When I was restoring a dump on 9.1.4 (created on 9.0.7) I got this error: ERROR: function imported_credentials_generalized_external_id(imported_email3) does not exist LINE 1: ...ed_external_i_idx ON imported_email3 USING btree (imported_c... ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. On this index creation statement: CREATE INDEX imported_email3_imported_credentials_generalized_external_i_idx ON imported_email3 USING btree (imported_credentials_generalized_external_id(imported_email3.*)); Looking on the function and index in the original database I found a very strange situation when the argument data type of the function differs from the type of the argument in the function's signature in the index. mirtesen-0-3=# \df imported_credentials_generalized_external_id List of functions -[ RECORD 1 ]---+- Schema | public Name| imported_credentials_generalized_external_id Result data type| text Argument data types | i_row imported_email Type| normal mirtesen-0-3=# \d imported_email3_imported_credentials_generalized_external_i_idx Index "public.imported_email3_imported_credentials_generalized_external_i_idx" Column| Type | Definition --+--+- imported_credentials_generalized_external_id | text | imported_credentials_generalized_external_id(imported_email3.*) btree, for table "public.imported_email3" I managed to reproduce this issue by creating another table with LIKE. mirtesen-0-3=# CREATE TABLE imported_email4 (LIKE imported_email3 INCLUDING ALL); NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "imported_email4_pkey" for table "imported_email4" CREATE TABLE And what I have found is that it just renamed the table name in the function's signature in the index. mirtesen-0-3=# \d imported_email4_imported_credentials_generalized_external_i_idx Index "public.imported_email4_imported_credentials_generalized_external_i_idx" Column| Type | Definition --+--+- imported_credentials_generalized_external_id | text | imported_credentials_generalized_external_id(imported_email4.*) btree, for table "public.imported_email4" I think it would be useful to do some checks here. Thank you. -- Sergey Konoplev a database architect, software developer at PostgreSQL-Consulting.com http://www.postgresql-consulting.com Jabber: gray...@gmail.com Skype: gray-hemp Phone: +79160686204 -- 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] Stored function signature incompatibility in index (probably a bug)
Sergey Konoplev writes: > When I was restoring a dump on 9.1.4 (created on 9.0.7) I got this error: > ERROR: function > imported_credentials_generalized_external_id(imported_email3) does not exist Yeah. Here's a more complete example on HEAD: regression=# create table foo(f1 int, f2 int); CREATE TABLE regression=# create function foosum(foo) returns int language sql as 'select $1.f1 + $1.f2' immutable ; CREATE FUNCTION regression=# create index fooi on foo (foosum(foo.*)); CREATE INDEX regression=# \d fooi Index "public.fooi" Column | Type | Definition +-+--- foosum | integer | foosum(foo.*) btree, for table "public.foo" regression=# create table foobar (like foo including indexes); CREATE TABLE regression=# \d foobar Table "public.foobar" Column | Type | Modifiers +-+--- f1 | integer | f2 | integer | Indexes: "foobar_foosum_idx" btree (foosum(foobar.*)) regression=# \d foobar_foosum_idx Index "public.foobar_foosum_idx" Column | Type |Definition +-+-- foosum | integer | foosum(foobar.*) btree, for table "public.foobar" While foobar_foosum_idx looks alright on first glance, it cannot be duplicated: regression=# create index foobari on foobar (foosum(foobar.*)); ERROR: function foosum(foobar) does not exist LINE 1: create index foobari on foobar (foosum(foobar.*)); ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. So the CREATE TABLE LIKE code is being far too cavalier about dealing with whole-row Vars in index definitions (and who knows where else). They don't have the same type in the cloned table as they did in the original. Some cases would probably still work all right, but not this usage. Also, a look into the pg_index entry shows that the whole-row Var for foobar.* is claimed to have vartype equal to foo's rowtype, which is flat out wrong, and could easily lead to crashes once either table had been altered to be different from the other. A potential workaround is illustrated by: regression=# create index foobari on foobar (foosum(row(foobar.*)::foo)); CREATE INDEX regression=# \d foobari Index "public.foobari" Column | Type |Definition +-+-- foosum | integer | foosum(ROW(f1, f2)::foo) btree, for table "public.foobar" but this seems like kind of a kluge. I'm not sure that we ought to insert such a thing rather than just throwing an error. 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] Stored function signature incompatibility in index (probably a bug)
On Thu, Jun 28, 2012 at 8:59 PM, Tom Lane wrote: > A potential workaround is illustrated by: > > regression=# create index foobari on foobar (foosum(row(foobar.*)::foo)); > CREATE INDEX > regression=# \d foobari > Index "public.foobari" > Column | Type | Definition > +-+-- > foosum | integer | foosum(ROW(f1, f2)::foo) > btree, for table "public.foobar" Thank you, Tom. It is a good idea. I need it for partitioning. So I am going to create a parent table and the first partition with this whole-row casted to the parent's type in index. Other partitions will be created by CREATE...LIKE. > but this seems like kind of a kluge. I'm not sure that we ought to > insert such a thing rather than just throwing an error. May be this issue and workaround is worth to be mentioned in docs besides throwing an error? -- Sergey Konoplev a database architect, software developer at PostgreSQL-Consulting.com http://www.postgresql-consulting.com Jabber: gray...@gmail.com Skype: gray-hemp Phone: +79160686204 -- 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] Postgresql 9.0.6 alway run VACUUM ANALYZE pg_catalog.pg_attribute
On Thu, 2012-06-28 at 21:41 +0700, tuanhoanganh wrote: > Hello > I am using PostgreSQL 9.0.6 64 bit on Windows 2003 64bit. > When i view Postgresql status, there are some autovaccum alway run. > Ex VACUUM ANALYZE pg_catalog.pg_attribute. > > > Is it problem of PostgreSQL? Please help me. Do you have activity on the database? If so, autovacuum is normal. Regards, Jeff Davis -- 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] create a script which imports csv data
On 06/28/2012 07:53 PM, Robert Buckley wrote: Hi, I have to create a script which imports csv data into postgresql ...and have a few questions about the best way to do it. The advice already given is pretty good. Remember you can always create a clean new table then INSERT INTO ... SELECT to populate it from a scratch table you loaded your CSV into, so you don't have to do your cleanups/transformations to the CSV or during the COPY its self. If it's a big job, it's going to be regular, you're going to have to merge it with more imports later, etc, consider an ETL tool like Penatho. http://kettle.pentaho.com/ For very very fast loading of bulk data, consider pg_bulkload http://pgbulkload.projects.postgresql.org/ . It's only worth the hassle if your load will take many, many hours without it. -- Craig Ringer
