[GENERAL] performance problem with loading data
Hi all.
I use PG 8.1.8 and PostGIS 1.1.1
vka1=# select version();
version
-
PostgreSQL 8.1.8 on x86_64-pc-linux-gnu, compiled by GCC cc (GCC)
4.1.220061115 (prerelease) (Debian
4.1.1-21)
vka1=# select postgis_full_version();
postgis_full_version
--
POSTGIS="1.1.1" GEOS="2.2.3-CAPI-1.1.1" PROJ="Rel. 4.4.9, 29 Oct 2004"
USE_STATS
I develop application for loading geodata to database. In separate
transaction the application inserts the data in separate table that created
dynamically when transaction started. All tables has equal structure.
Geodata has simple and similar structure (field geometry): POLYGON((x1 y1,
x2 y2, x3 y3, x4 y4, x1 y1))
For loading geodata I use INSERT into via LIBPQ.
In big loop I call PQexec(conn, query).
I found *VERY* strange problem: speed of the loading process is slow down
(first 1 objects are loaded in 69792 ms and last 1 objects in 123737
ms). And futhermore, if I do NOT close current session but start new
transaction, the first 1 objects will be loaded in 192279 ms and last
1 objects in 251742 ms. And so on!! :-(
But if I drop the trigger speed of loading process is NOT slow down.
Who can explain me what I do incorrect?
Thanks in advance
Sergey Karin
===
code===
===
create table (
GID SERIAL not null,
GEOM_ORDER INT4not null default 0,
ZOOMABLE BOOL not null default false,
GEOM GEOMETRY
constraint primary key (GID),
);
create index on using gist ( geom
gist_geometry_ops );
create trigger trgOInsert
before insert or update
on
for each row
execute procedure oInsertCheck('GEOMETRYCOLLECTION', 0);
create or replace function oInsertCheck() returns trigger as'
declare
g_isvalid boolean;
iSrid int4;
geomType varchar;
begin
if(new.geom isnull) then
new.geom := geomFromText(\'GEOMETRYCOLLECTION(EMPTY)\');
end if;
if(new.geom_order isnull) then
new.geom_order := 0;
end if;
select isvalid(new.geom) into g_isvalid;
if(g_isvalid isnull) then
return NULL;
end if;
geomType := TG_ARGV[TG_NARGS-2];
iSrid:= TG_ARGV[TG_NARGS-1];
if(upper(geomType) = \'GEOMETRYCOLLECTION\') then
new.geom := force_collection(new.geom);
end if;
new.geom := setSrid(new.geom, iSrid);
return new;
end
'language 'plpgsql' security definer;
=end of code==
[GENERAL] pg_catalog.pg_get_serial_sequence() returns NULL
Hi!
I use PG 8.1.5
I execute in psql next comands:
create table t_table (gid serial, name varchar);
select pg_catalog.pg_get_serial_sequence('t_table', 'gid');
pg_get_serial_sequence
public.t_table_gid_seq
create table t_table_1() inherits (t_table);
\d t_table_1
Table "public.t_table_1"
column | Type| Модификаторы
-+---+---
gid | integer | not null default
nextval('t_table_gid_seq'::regclass)
name| character varying |
Inherit: t_table
select pg_catalog.pg_get_serial_sequence('t_table_1', 'gid');
pg_get_serial_sequence
How I can get name of sequence used by gid column of partition table? And
why for t_table_1 pg_get_serial_sequence() returns NULL?
It returns NULL also if I create new sequence manually and use ALTER TABLE
ALTER COLUMN SET DEFAULT nextval(::regclass).
In http://www.postgresql.org/docs/8.1/static/datatype.html#DATATYPE-SERIAL I
can read that type SERIAL is equivalent to
CREATE SEQUENCE *tablename*_*colname*_seq;
CREATE TABLE *tablename* (
*colname* integer DEFAULT nextval('*tablename*_*colname*_seq') NOT NULL
);
Thanks in advance
Sergey Karin
[GENERAL] Question about Large Objects
Hi, all As I understood PostgreSQL allows to store large objects 2GB size maximum. Are there any plans to increase or removing that limitation? If no, are there any abilities to store 10-20GB raster data (aero foto image) in postgreSQL? Thanks Sergey Karin
[GENERAL] problem with bit(n) type
Hi, List! kosten=# select version(); version --- PostgreSQL 8.0.0rc5 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2.3 (1 row) kosten=# create table foo(t bit(4)); CREATE TABLE kosten=# insert into foo values(B'0011'); INSERT 985996 1 kosten=# select t from foo; t -- 0011 (1 row) But if I do something like this: kosten=# select t::text from foo; ERROR: cannot cast type bit to text I get an error. Okay the system cannot cast bit to text... But this is the big problem for me... Is there a way to cast bit(n) to text or varchar? thanks in advance. Sergey Karin PS. I have found that there are some functions: varbit_in() and varbit_out(). But they works with internal type cstring. And I cannot cast cstring to varchar or text. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[GENERAL] [8.1] "drop table" in plpgsql function
hi all! I have found a strange error. panorama2=# select version(); version PostgreSQL 8.1.0 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2.3 (1 row) I have next function in file ./f_safe_drop_table.sql: -- create or replace function f_safe_drop_table(varchar) returns bool as' declare table_name alias for $1; tcount int4; begin select f_is_table_exist(table_name) into tcount; if(tcount > 0) then drop table table_name; return true; end if; return false; end 'language 'plpgsql'; == when I create my function in psql, I have next error: --- panorama2=# \i ./f_safe_drop_table.sql psql:./f_safe_drop_table.sql:29: ERROR: syntax error at or near "$1" at character 13 QUERY: drop table $1 CONTEXT: SQL statement in PL/PgSQL function "f_safe_drop_table" near line 10 psql:./Functions/Misc/f_safe_drop_table.sql:29: LINE 1: drop table $1 psql:./Functions/Misc/f_safe_drop_table.sql:29: ^ --- But if I replace "drop table table_name" on "dyn_query := 'drop table ' || table_name; execute dyn_query" all works fine! -- create or replace function f_safe_drop_table(varchar) returns bool as' declare table_name alias for $1; tcount int4; dyn_query varchar; begin select f_is_table_exist(table_name) into tcount; if(tcount > 0) then dyn_query := 'drop table ' || table_name; execute dyn_query; return true; end if; return false; end 'language 'plpgsql'; == panorama2=# \i ./f_safe_drop_table.sql CREATE FUNCTION Also both functions on PG8.0 works fine! Sergey Karin ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[GENERAL] [8.1] "drop table" in plpgsql function
-- Forwarded message -- From: Sergey Karin <[EMAIL PROTECTED]> Date: 11.11.2005 13:59 Subject: [8.1] "drop table" in plpgsql function To: pgsql-general@postgresql.org hi all! I have found a strange error. panorama2=# select version(); version PostgreSQL 8.1.0 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2.3 (1 row) I have next function in file ./f_safe_drop_table.sql: -- create or replace function f_safe_drop_table(varchar) returns bool as' declare table_name alias for $1; tcount int4; begin select f_is_table_exist(table_name) into tcount; if(tcount > 0) then drop table table_name; return true; end if; return false; end 'language 'plpgsql'; == when I create my function in psql, I have next error: --- panorama2=# \i ./f_safe_drop_table.sql psql:./f_safe_drop_table.sql:29: ERROR: syntax error at or near "$1" at character 13 QUERY: drop table $1 CONTEXT: SQL statement in PL/PgSQL function "f_safe_drop_table" near line 10 psql:./Functions/Misc/f_safe_drop_table.sql:29: LINE 1: drop table $1 psql:./Functions/Misc/f_safe_drop_table.sql:29: ^ --- But if I replace "drop table table_name" on "dyn_query := 'drop table ' || table_name; execute dyn_query" all works fine! -- create or replace function f_safe_drop_table(varchar) returns bool as' declare table_name alias for $1; tcount int4; dyn_query varchar; begin select f_is_table_exist(table_name) into tcount; if(tcount > 0) then dyn_query := 'drop table ' || table_name; execute dyn_query; return true; end if; return false; end 'language 'plpgsql'; ====== panorama2=# \i ./f_safe_drop_table.sql CREATE FUNCTION Also both functions on PG8.0 works fine! Sergey Karin ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] FOREIGN KEY CONSTRAINT AND INHERITANCE
Hi, All! I have the tables as defined: create table style ( ID_STYLE INT4 not null, STYLE_STRING VARCHAR not null, constraint PK_STYLE primary key (ID_STYLE) ); create table style_type_object ( ID_TYPE_OBJECT INT4 not null, ID_STYLE INT4 not null, ZOOMABLE BOOL not null default false, START_SCALE INT4 not null, END_SCALEINT4 not null ); alter table STYLE_TYPE_OBJECT add constraint FK_ID_STYLE foreign key (ID_STYLE) references STYLE (ID_STYLE) on delete restrict on update restrict; create table style_1 ( constraint PK_STYLE_1 primary key (id_style), check (id_style between 1 and 1) ) inherits (style); create rule r_style_1 as on insert to style where (id_style between 1 and 1) do instead insert into style_1 (id_style, style_string) values(new.id_style, new.style_string); insert into style values (2, 'PEN(w:1px)' ); vka7=# select id_style, style_string from style; id_style | style_string --+-- 2 | PEN(w:1px) vka7=# insert into style_type_object values(1, 2, false, 0, 0); ERROR: insert or update on table "style_type_object" violates foreign key constraint "fk_id_style" DETAIL: Key (id_style)=(2) is not present in table "style". THE QUESTION IS: are there any abilities to check foreign key constraint not only in master table, but also in all tables that inherited the master? Thanks in advance. Sergey Karin
[GENERAL] Please, help! About database cluster and adding to it additional disk space
Hi, List! Lets suppose the next situation... We create database cluster (via initdb) on some disk. Then we create and use a database that can be very large. In some moment the database occupy all disk space allicated to cluster. The question is: are there any abilities in PostgreSQL to use for created cluster additional disk space in new other disk? Similar ability exist in Informix... Thanks in advance for any answers! Sergey Karin
[GENERAL] question about large databases
Hi, List! Some times ago in this list was discussed next question: Which databases are small and which are large? The answer was: 1-2 GB - small 50 and around - large As I think, hundreds of GB and more - very large (VLDB). How Postgres works with VLDB? And what about speed? Can I expect that postgres works with VLDB not worse that commertial DBMS (DB2, Oracle, Informix). Maybe someone have a comparision results of Postgres, DB2, Oracle and Informix (or postgres and any of commertial DBMS) when this DBMS manage with VLDB? I will VERY, VERY respect to that man for the information... My organisation plans to use GIS based on PostGIS/Postgres. As we think, size of our database can be 50, 100 and more Gb. Thanks in advance! Sergey Karin
[GENERAL] triggers, rules and alter table
Hi, List! I'm using pg 8.1.0 this version allows to create triggers on INSERT, DELETE and UPDATE and also allows to create rules on SELECT, UPDATE, INSERT and DELETE. But I want to do some actions on ALTER TABLE ... ADD COLUMN and ALTER TABLE ... DROP COLUMN events. How I can handle that events in 8.1.x version? Sergey Karin
[GENERAL] triggers and SELECT
Hi, List! Are there any plans to realize triggers on SELECT in new versions of PG? Sergey Karin
[GENERAL] NULL values and string
Hi, List! I'm using PG8.1. Are there any abilities to represent NULL values as string? I'm doing something like this: create function func(int4) returns varchar as' declare num_value alias for $1; string_value varchar; begin string_value := \'input value = \' || num_value; return string_value; end 'language 'plpgsql'; If I einvoke my function with NULL argument, it return NULL. But I want 'input value = NULL'. Of course, I can check input value like this: if(num_value isnull) then string_value := \'input value = NULL\'; else string_value := \'input_value = \' || num_value; end if; But it is not laconic... Sergey Karin
[GENERAL] change current user in pl/pgsql function
Hi, List!I use PG8.1Are there any abilities to change current user during pl/pgsql function execution?I developed function that have 'security definer' option and created under superuser. But for executing some command in the function I need to change user to session_user and after execotion change user to superuser. Sergey Karin
[GENERAL] alter superuser...
Hi, List!I alter user postgres, that was a superuser in my db cluster. And now it is not a superuser. There are no superusers in my cluster. Yes, I am stupid :)But are there any abilities to restore superuser excluding initdb? Sergey Karin
[GENERAL] PG and Tivoli Data Protection
Hi All!Does anybody use Postgres with storage managers like Tivoli Data Protection? Are there aby abilities for that in PG (I use 8.1)?Sergey Karin.
[GENERAL] How to detect primary key of a table
Hi, List!I use PG8.1Are there any abilities to detect primary key of a table?Sergey Karin
Re: [GENERAL] How to detect primary key of a table
Yes, it is not a problem via psql.But I want to detect primary key via libpq (e.g using select commands)2006/3/30, Michael Kleiser < [EMAIL PROTECTED]>:\d your_tablewill show it>> create table foobar ( foo int primary key, bar int ); NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index"foobar_pkey" for table "foobar"CREATE TABLE>> \d foobarTable "public.foobar" Column | Type | Modifiers +-+--- foo| integer | not null bar| integer |Indexes:"foobar_pkey" PRIMARY KEY, btree (foo)From: [EMAIL PROTECTED][mailto:[EMAIL PROTECTED]] On Behalf Of Sergey KarinSent: Thursday, March 30, 2006 1:13 PMTo: pgsql-general@postgresql.orgSubject: [GENERAL] How to detect primary key of a tableHi, List!I use PG8.1Are there any abilities to detect primary key of a table?Sergey Karin
