[GENERAL] failed insert queries to one table-postgis enabled db
I have two type of INSERT queries that keep getting errors no matter how many times I run it in pgadminIII 1.8.4 PostgresQL 8.3.4+PostGIS1.3.5 on centos5 Linux machine. 1. execution of 18MB INSERT queries to one empty table failed in postgis enabled database.sample one of the thousands queries : INSERT INTO lutons (gid, area, perimeter, map1a_, map1a_id, no_id, no_id2, main, use_bgn, unit, lvls, hght, type_bgn, no_, name_biz, addrss, biz2, code, bgn, code_bgn, builds, note, the_geom) VALUES (1, 1980.087, 179.1345000, 63, 0, '1001001', '1001001', 'Trade', 'Office', 1.000, NULL, '13', 'one', NULL, 'Wayne Co.', 'Elm Street', 'Office', '836', 'Good', 1, 'm', NULL, '010600010001030001000700CECAEFA1EEA34BC0F618A264232F69C0BF39315DC29748C01E53771C995369C0B0B8C50181173AC080DEED01F6D16BC0D28922835C2450C08EB4F082865E70C048D67B9E1CDA56C09B5ECFBE2B756DC0AF909ABFC40A4DC04CF2D285506169C0CECAEFA1EEA34BC0F618A264232F69C0'); in the Messages tab console in pgadminIII 1.8.4 prompts this error after 30-60 seconds of executing those queries ERROR: geometry contains non-closed rings ** Error ** ERROR: geometry contains non-closed rings SQL state: XX000 == 2. 16mb insert failed . the problem query ; INSERT INTO lutonszone (gid, area, perimeter, lot_no, unitadmin, zoning, notes, the_geom, projectid) VALUES (23071, 3.071000, -.00, '0036\0178', 46, 'ru4', NULL, '010620E610010001030001000400C61858C7F112594099D87C5C1BDA154072A43330F2125940CC7D721420DA154049F7730AF21259',NULL); in the Messages tab console : WARNING: nonstandard use of escape in a string literal LINE 23237: ...projectid) VALUES (23071, 3.071000, -.00, '0036\0178... ^ HINT: Use the escape string syntax for escapes, e.g., E'\r\n'. ERROR: geometry requires more points ** Error ** ERROR: geometry requires more points SQL state: XX000
[GENERAL] SQL Source Search for Emacs
Hi, I've submitted[1] some useful portions of my SQL editing hacks for emacs. I generally use them for navigating PostgreSQL (dump) scripts, but it works for other RDBMSes as well. I hope it would work for you as well. Regards. [1] http://www.emacswiki.org/emacs/SQLSourceSearch -- 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] very long update gin index troubles back?
Ответишь ему что-нибудь? Он так мутно излагает, что я ни хрена не понял. Ivan Sergio Borgonovo wrote: I've a table that contain a tsvector that is indexed (gin) and triggers to update the tsvector that should then update the index. This gin index has always been problematic. Recreation and updates were very slow. Now I had to update 1M rows of that table but for columns that doesn't involve the tsvector I dropped the trigger to update the tsvector so that when rows get updated the trigger won't be called so things should be faster... but still it is taking forever. begin; set constraints all deferred; select * from FT1IDX_trigger_drop(); update catalog_items set APrice=p.PrezzoA, BPrice=p.PrezzoB from import.catalog_prices p where catalog_items.ItemID=p.id; select * from FT1IDX_trigger_create(); commit; function are used since I've 2 triggers actually that I drop and create. Is there anything wrong in the above to make this update so slow on a 2x Xeon 3.2GHz 4GbRAM and a RAID1 [sic] I know it is slow on write. -- Teodor Sigaev E-mail: teo...@sigaev.ru WWW: http://www.sigaev.ru/ -- 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] failed insert queries to one table-postgis enabled db
On Jan 24, 2009, at 9:13 AM, m zyzy wrote: I have two type of INSERT queries that keep getting errors no matter how many times I run it in pgadminIII 1.8.4 PostgresQL 8.3.4+PostGIS1.3.5 on centos5 Linux machine. 2. 16mb insert failed . the problem query ; INSERT INTO lutonszone (gid, area, perimeter, lot_no, unitadmin, zoning, notes, the_geom, projectid) VALUES (23071, 3.071000, -.00, '0036\0178', 46, 'ru4', NULL, '010620E610010001030001000400C61858C7F112594099D87C5C1BDA154072A43330F2125940CC7D721420DA154049F7730AF21259 ',NULL); in the Messages tab console : WARNING: nonstandard use of escape in a string literal LINE 23237: ...projectid) VALUES (23071, 3.071000, -.00, '0036\0178... ^ HINT: Use the escape string syntax for escapes, e.g., E'\r\n'. ERROR: geometry requires more points ** Error ** Looks like the backslash is interpreted as an escape character. Switch on 'standard_conforming_strings' and probably turn off 'escape_string_warning'. Don't know about the other one, I'm guessing you're supplying a polygon or some other shape that is missing a closing segment (and thus being a polyline effectively I guess). I've never worked with shapes in postGIS though. It could be caused by the above issue if parts of the shape definition contain backslashes as well. And finally, I know there is a limit on query size somewhere... You might be hitting that with 16MB queries. Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll see there is no forest. !DSPAM:737,497af707747031347810546! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Installing PostgreSQL on Windows 7 Beta Build 7000 64bit - first results
Hello, my first results of installation-attempts of PostgreSQL 8.3.5 from pginstaller on Windows 7 64bit: 1.) after getting up to "initialize database cluster", Dialog appears: "Secondary Logon" Service not running. Please start this service and retry Maybe some "net start seclogon" would be helpfull if W7 is detected by installer? 2) installation copies a lot of stuff, moves up to "services get started", which takes A LOT OF TIME as of logfile: [] MSI (s) (F0:C4) [12:29:49:297]: Executing op: ActionStart(Name=StartServices,Description=Dienste werden gestartet,Template=Dienst: [1]) Aktion 12:29:49: StartServices. Dienste werden gestartet MSI (s) (F0:C4) [12:29:49:298]: Executing op: ProgressTotal(Total=1,Type=1,ByteEquivalent=130) MSI (s) (F0:C4) [12:29:49:298]: Executing op: ServiceControl(,Name=pgsql-8.3,Action=1,Wait=1,) StartServices: Dienst: PostgreSQL Database Server 8.3 and finally fails with "... service could not be startet, please check if you have enough rights to start system services" [retry] / [cancel] (btw, I am running the installer from an administrator prompt) "retry" gives another time period to meditate & contemplate & wait; "Cancel" leads to rollback of the installation: MSI (s) (F0:C4) [12:29:49:297]: Executing op: ActionStart(Name=StartServices,Description=Dienste werden gestartet,Template=Dienst: [1]) Aktion 12:29:49: StartServices. Dienste werden gestartet MSI (s) (F0:C4) [12:29:49:298]: Executing op: ProgressTotal(Total=1,Type=1,ByteEquivalent=130) MSI (s) (F0:C4) [12:29:49:298]: Executing op: ServiceControl(,Name=pgsql-8.3,Action=1,Wait=1,) StartServices: Dienst: PostgreSQL Database Server 8.3 MSI (s) (F0:C4) [12:33:25:956]: Note: 1: 2205 2: 3: Error MSI (s) (F0:C4) [12:33:25:956]: Note: 1: 2228 2: 3: Error 4: SELECT `Message` FROM `Error` WHERE `Error` = 1920 MSI (c) (DC:50) [12:33:25:958]: Note: 1: 2205 2: 3: Error MSI (c) (DC:50) [12:33:25:958]: Note: 1: 2228 2: 3: Error 4: SELECT `Message` FROM `Error` WHERE `Error` = 2835 DEBUG: Error 2835: The control ErrorIcon was not found on dialog ErrorDlg Bei der Installation dieses Pakets ist ein unerwarteter Fehler aufgetreten. Es liegt eventuell ein das Paket betreffendes Problem vor. Der Fehlercode ist 2835. Argumente: ErrorIcon, ErrorDlg, Fehler 1920. Der Dienst PostgreSQL Database Server 8.3 (pgsql-8.3) konnte nicht gestartet werden. Überprüfen Sie, ob Sie ausreichende Berechtigungen zum Starten von Systemdiensten besitzen. MSI (s) (F0:18) [12:34:43:812]: I/O on thread 3064 could not be cancelled. Error: 1168 MSI (s) (F0:18) [12:34:43:812]: I/O on thread 2040 could not be cancelled. Error: 1168 MSI (s) (F0:18) [12:34:43:812]: I/O on thread 3636 could not be cancelled. Error: 1168 MSI (s) (F0:18) [12:34:43:812]: I/O on thread 3268 could not be cancelled. Error: 1168 MSI (s) (F0:18) [12:34:43:812]: I/O on thread 3776 could not be cancelled. Error: 1168 MSI (s) (F0:18) [12:34:43:812]: I/O on thread 2516 could not be cancelled. Error: 1168 MSI (s) (F0:18) [12:34:43:812]: I/O on thread 3848 could not be cancelled. Error: 1168 MSI (s) (F0:C4) [12:34:43:813]: Note: 1: 2205 2: 3: Error MSI (s) (F0:C4) [12:34:43:813]: Note: 1: 2228 2: 3: Error 4: SELECT `Message` FROM `Error` WHERE `Error` = 1709 MSI (s) (F0:C4) [12:34:43:813]: Produkt: PostgreSQL 8.3 -- Fehler 1920. Der Dienst PostgreSQL Database Server 8.3 (pgsql-8.3) konnte nicht gestartet werden. Überprüfen Sie, ob Sie ausreichende Berechtigungen zum Starten von Systemdiensten besitzen. MSI (s) (F0:C4) [12:34:43:813]: Note: 1: 2205 2: 3: Error MSI (s) (F0:C4) [12:34:43:813]: Note: 1: 2228 2: 3: Error 4: SELECT `Message` FROM `Error` WHERE `Error` = 1602 MSI (c) (DC:50) [12:34:43:816]: Note: 1: 2205 2: 3: Error MSI (c) (DC:50) [12:34:43:816]: Note: 1: 2228 2: 3: Error 4: SELECT `Message` FROM `Error` WHERE `Error` = 2835 DEBUG: Error 2835: The control ErrorIcon was not found on dialog ErrorDlg Bei der Installation dieses Pakets ist ein unerwarteter Fehler aufgetreten. Es liegt eventuell ein das Paket betreffendes Problem vor. Der Fehlercode ist 2835. Argumente: ErrorIcon, ErrorDlg, Sind Sie sicher, dass Sie den Vorgang abbrechen möchten? Aktion beendet um 12:34:46: InstallFinalize. Rückgabewert 3. MSI (s) (F0:C4) [12:34:46:169]: User policy value 'DisableRollback' is 0 MSI (s) (F0:C4) [12:34:46:169]: Machine policy value 'DisableRollback' is 0 MSI (s) (F0:C4) [12:34:46:172]: Executing op: Header(Signature=1397708873,Version=500,Timestamp=976774067,LangId=1033,Platform=0,ScriptType=2,ScriptMajorVersion=21,ScriptMinorVersion=4,ScriptAttributes=1) MSI (s) (F0:C4) [12:34:46:172]: Executing op: DialogInfo(Type=0,Argument=1033) MSI (s) (F0:C4) [12:34:46:173]: Executing op: DialogInfo(Type=1,Argument=PostgreSQL 8.3) MSI (s) (F0:C4) [12:34:46:173]: Executing op: RollbackInfo(,RollbackAction=Rollback,RollbackDescription=Aktion wird rückgängig gemacht:,RollbackTemplate=[1],CleanupAction=RollbackCleanup,CleanupDescription=Sicherungsdateien werden entfernt,Cleanup
Re: [GENERAL] very long update gin index troubles back?
On Fri, 23 Jan 2009, Ivan Sergio Borgonovo wrote: I've a table that contain a tsvector that is indexed (gin) and triggers to update the tsvector that should then update the index. This gin index has always been problematic. Recreation and updates were very slow. Now I had to update 1M rows of that table but for columns that doesn't involve the tsvector I dropped the trigger to update the tsvector so that when rows get updated the trigger won't be called so things should be faster... but still it is taking forever. all indexes will be updated, so if you might want to drop indexes and rebuild from the scratch. begin; set constraints all deferred; select * from FT1IDX_trigger_drop(); update catalog_items set APrice=p.PrezzoA, BPrice=p.PrezzoB from import.catalog_prices p where catalog_items.ItemID=p.id; select * from FT1IDX_trigger_create(); commit; function are used since I've 2 triggers actually that I drop and create. Is there anything wrong in the above to make this update so slow on a 2x Xeon 3.2GHz 4GbRAM and a RAID1 [sic] I know it is slow on write. Regards, Oleg _ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: o...@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83 -- 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] very long update gin index troubles back?
GIN index is slow for update by its construction. When you update the rows with or without columns indexed by GIN, postgres (in most cases) will insert new records, so index insertion will occur. So, for large updates it's much cheaper to drop and create index. That was a one of reasons to develop fast_insert_gin patch which now in review process. Ivan Sergio Borgonovo wrote: I've a table that contain a tsvector that is indexed (gin) and triggers to update the tsvector that should then update the index. -- Teodor Sigaev E-mail: teo...@sigaev.ru WWW: http://www.sigaev.ru/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] dblink syntax question for remotely invoking void-returning procedures
Hello everyone,
I've got some questions about dblink that I couldn't find answers to in the
documentation. Any help would be much appreciated.
I need to invoke a function on a remote server that returns either void or
text, and I'm trying to find a nice way of doing it.
My test schema on the remote DB:
create table test (id serial, val text)
create or replace function inserttest() returns void as $$
begin
insert into test (val) values ('x');
end;
$$ language 'plpgsql'
First I create a dblink connection to the above DB, then I try to call
inserttest().
I've tried all the below queries in the console and in PL/pgSQL, two of
which work correctly and *then* throw an error, which I think may be a bug.
>From the console, this works:
select * from dblink('test', 'select inserttest()') as tmp(result text)
However, if I execute this within a PL/pgSQL procedure, the error message is
ERROR: query has no destination for result data
HINT: If you want to discard the results of a SELECT, use PERFORM instead.
Needless to say, I tried listening to hints and the perform doesn't work in
this case.
The only result I've come up with is to modify the above function to
"returns integer", always zero, and do this:
select result into junk from dblink('test', 'select inserttest()') as
tmp(result integer)
This isn't ideal because a) it needs a junk variable, b) it's verbose and c)
void methods must return useless information. Is there a better way to do
the above in PL/pgSQL?
Many thanks in advance,
- Ian
(for completeness' sake, and to prove I read the manual :-), the things I
tried from the console and from procedures, and the results of each attempt
follows).
--WORKS, but throws error
select * from dblink('test', 'select inserttest()') as t1(test void);
-> column "test" has pseudo-type void
--WORKS, but throws error
select * from dblink('test', 'select inserttest()') as t1(test text);
-> query has no destination for result data
perform dblink('test', 'select inserttest()');
-> function returning record called in context that cannot accept type
record
select dblink('test', 'select inserttest()');
-> function returning record called in context that cannot accept type
record
select * from dblink('test', 'select inserttest()');
-> a column definition list is required for functions returning "record"
select * from dblink('test', 'select inserttest()') as void;
-> a column definition list is required for functions returning "record"
perform dblink_exec('test', 'select inserttest()');
-> statement returning results not allowed
perform dblink('test', 'select inserttest()');
-> ERROR: function returning record called in context that cannot accept
type record
-> CONTEXT: SQL statement "SELECT dblink('test', 'select inserttest()')"
perform dblink('test', 'perform inserttest()');
-> ERROR: sql error
-> DETAIL: ERROR: syntax error at or near "perform"
-> LINE 1: perform inserttest()
perform dblink('test', 'inserttest()');
-> ERROR: sql error
-> DETAIL: ERROR: syntax error at or near "inserttest"
-> LINE 1: inserttest()
select dblink_exec('test', 'perform inserttest()');
-> ERROR: sql error
-> SQL state: 42601
-> Detail: ERROR: syntax error at or near "perform"
-> LINE 1: perform inserttest()
Re: [GENERAL] very long update gin index troubles back?
On Sat, 24 Jan 2009 15:54:37 +0300 Teodor Sigaev wrote: > GIN index is slow for update by its construction. When you update > the rows with or without columns indexed by GIN, postgres (in most > cases) will insert new records, so index insertion will occur. So, > for large updates it's much cheaper to drop and create index. > > That was a one of reasons to develop fast_insert_gin patch which > now in review process. Somehow the update succeeded... but for better preparing to the future... I'm not sure I understood, so I'll provide more details. I've something like: create table t1( agg tsvector, a varchar(10), b varchar(10), c varchar(10), d int ); then 2 triggers that for every update to t1 fill up agg. agg:=tsvactor(coalesce(a,'')) || tsvactor(coalesce(b,'')) || tsvactor(coalesce(c,'')); and I have a gin index on agg. No matter if I drop the trigger that update agg content and the fact that I'm just updating d, postgresql will update the index? Right? -- Ivan Sergio Borgonovo http://www.webthatworks.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] conditional execution of insert/update-s
On 2009-01-22, Dmitry Teslenko wrote: > Hello! > Question is what's the easiest way to implement conditional execution > of insert/update-s via psql interface? > > As far as I know there's following options: > 1) create function in procedural language (plpgsql, for example); > then call that function > 2) update ... where ; insert ... (select ... where ); > not sure here if can be unrelated to the table being modified > by update/insert statement; they can be unrelated > Are there alternatives? here's one but it's messy when the condition is false. BEGIN; SELECT expression_which_causes_an_exception_when_condition_fails; statements...; COMMIT; the advantage of this is that it can be used with DDL statements. -- 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] difference between current_timestamp and now() in quotes
On 2009-01-22, Adrian Klaver wrote: > On Thursday 22 January 2009 8:16:46 am Alvaro Herrera wrote: >> Grzegorz Jaśkiewicz escribió: >> > test2=# insert into dupa(a) select 'current_timestamp' from >> > generate_series(1,100); >> > ERROR: date/time value "current" is no longer supported >> > LINE 1: insert into dupa(a) select 'current_timestamp' from generate... >> >^ >> > test2=# insert into dupa(a) select 'now()' from generate_series(1,100); >> > INSERT 0 100 >> > >> > >> > Any ideas why the difference ? >> >> The parser handles CURRENT_TIMESTAMP (and others) specially, and doesn't >> recognize it in quotes. I don't know why 'now()' works; I think it is a >> literal of type unknown. I guess it's expanded to the actual value in >> later parsing stages. >> >> -- >> Alvaro Herrerahttp://www.CommandPrompt.com/ >> PostgreSQL Replication, Consulting, Custom Development, 24x7 support > > At least on 8.2 'now()' does not work either at least not in the way I think > you want. I get: > > test=# SELECT 'now()'; > ?column? > -- > now() > (1 row) 'now' is a string which can be translated to timestamp now() is a function that returns the current timestamp current_timestamp is a constant that does the same -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Re: how to implement a foreign key type constraint against a not unique column
On 2009-01-21, Brent Wood wrote: > Hi, > > I have a table with a column of ID's (integer), these are unique > except where they = -1 (column 1) > I have a partial unique index where the value is not -1 to enforce this. > I want to use this column as a foreign key on a column in another table > (column 2), but cannot without a full unique index. a full unique index is easy use an expression that's null for -1. create unique index foobar on foo( ( case fee when -1 then null else fee end ) ); > Is there any way to add an equivalent constraint to a foreign key which > restricts entries in column 2 to values in column 1? unfortunately expressions don't seem to be allowed for foreign key constraints you could add an auxillary column and create a rule, or trigger to keep it updated. -- 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] problem converting database to UTF-8
On 2009-01-22, David Goodenough wrote: > I have a database which was created as LATIN1 (the machine has the > wrong locales installed when I set up PG). It is running 8.3. > > So I found various places which said the way to do this was to do > a pg_dumpall -f dump_file, get rid of the entire database, init_db -E UTF-8, > and then psql -f dumpfile. > > But the psql fails saying:- > > psql:dumpfile:49: ERROR: encoding LATIN1 does not match server's locale > en_GB.UTF-8 > DETAIL: The server's LC_CTYPE setting requires encoding UTF8. > > I have en_GB.UTF-8 now as my primary locale, and en_GB.ISO8859-1 is > also generated. > > So I looked around again and found people saying I needed to use iconv, > but that does not help, I get the same error. > Is there a definative HOWTO that I can follow, if not does someone > have a set of instructions that will work? simplest best way is to dump the individual databases and then restore them individually. else you need to go theough the dump file and doctor the CREATE DATABASE lines to use UTF-8 (or not specify an encoding) whilst making sure that CLIENT_ENCODING remains set to LATIN1 (so that the data from the dump file remains intelligible) alternately you may be able to use iconv to convert the whole file to UTF8 and also all ocurrances of LATIN1 in commands with UTF8 The frst thing I'd do is look in the dump file and see what's on line 49. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
