[GENERAL] SQL Server access from PostgreSQL

2015-05-18 Thread Filip Rembiałkowski
Hi. I will be happy to hear your opinion which one is better - odbc_fdw or tds_fdw? In terms of performance / stability / convenience. (Pg on OpenSuse, MS SQL on Win2008 ) Thanks! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http

Re: [GENERAL] SQL Server access from PostgreSQL

2015-05-18 Thread Filip Rembiałkowski
ilip On Mon, May 18, 2015 at 6:23 PM, Geoff Montee wrote: > Hi Filip, > > On Mon, May 18, 2015 at 7:52 AM, Filip Rembiałkowski > wrote: >> Hi. >> >> I will be happy to hear your opinion which one is better - odbc_fdw or >> tds_fdw? >> >> In terms o

[GENERAL] Foreign data wrappers and indexes on remote side

2015-06-26 Thread Filip Rembiałkowski
Hi. Is there any way to take use of indexes on foreign tables? Currently (at least with tds_fdw, that I was testing) the planner just does a dumb full sequential scan in all cases. That is SELECT drink FROM foreignbar; -- takes as much time as SELECT drink FROM foreignbar where drink_key = 32

Re: [GENERAL] Foreign data wrappers and indexes on remote side

2015-06-26 Thread Filip Rembiałkowski
Is WHERE clause push-down implemented in any known fdw? Thank you. On Fri, Jun 26, 2015 at 6:19 PM, Tom Lane wrote: > =?UTF-8?Q?Filip_Rembia=C5=82kowski?= writes: >> Is there any way to take use of indexes on foreign tables? > >> Currently (at least with tds_fdw, that I was testing) the plann

Re: [GENERAL] stable functions

2007-05-31 Thread Filip Rembiałkowski
ce_ for each distinct value of param. -- Filip Rembiałkowski ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/

Re: [GENERAL] stable functions

2007-05-31 Thread Filip Rembiałkowski
nder "33.6. Function Volatility Categories". For me it's clear enough. -- Filip Rembiałkowski ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED]

Re: [GENERAL] Foreign keys and indexes

2007-06-05 Thread Filip Rembiałkowski
ate indexes manually if you need them. -- Filip Rembiałkowski ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match

Re: [GENERAL] When should I worry?

2007-06-11 Thread Filip Rembiałkowski
over time. You will be able to predict the point at which your system will be too slow to use, if indeed the performance degradation is expontential. Could you please share some details about this "tool like R"? Maybe some links or usage examples? TIA. -- Filip Rembiałkows

[GENERAL] create table liek view

2007-08-07 Thread Filip Rembiałkowski
me rule ON SELECT... -- Filip Rembiałkowski ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match

Re: [GENERAL] Controlling locale and impact on LIKE statements

2007-09-05 Thread Filip Rembiałkowski
st .Net .NZ Ltd, PO Box 11-053, Manners St, Wellington > WEB: http://catalyst.net.nz/ PHYS: Level 2, 150-154 Willis St > NZ: +64(4)916-7224MOB: +64(21)364-017UK: 0845 868 5733 ext 7224 > Make things as simple as possible, but no simpler - Einstein > ---

Re: [GENERAL] work hour calculations

2007-09-07 Thread Filip Rembiałkowski
2007-07-06 15:50:00+10 | 2007-07-09 07:10:00+10 | 01:20:00 > > 2007-07-07 12:30:00+10 | 2007-07-09 07:20:00+10 | 00:20:00 Could you please show the data structures ( create table commmands + ane constraints you have )? If you do this, you have much bigger chance of getting an answer :) --

Re: [GENERAL] Connection Pooling directly on Postgres Server

2007-09-08 Thread Filip Rembiałkowski
to implement without rewriting the protocol itself or disrupting existing behaviour. -- Filip Rembiałkowski ---(end of broadcast)--- TIP 6: explain analyze is your friend

Re: [GENERAL] work hour calculations

2007-09-09 Thread Filip Rembiałkowski
ime - pointer ) BUT: this is ugly. do you always assume that people are doing ONLY one task at a time? maybe think of representing work sheets in the database? maybe think of adding "work_time" field to your table (why not trust people, they know best) maybe the application you use f

Re: [GENERAL] PostgreSQL Glossary?

2007-09-16 Thread Filip Rembiałkowski
2007/9/13, Nikolay Samokhvalov <[EMAIL PROTECTED]>: > Hi all, > > does anybody know where to find a good list of PostgreSQL terms > (including both traditional terms and Postgres-specific ones)? Yes. Use http://www.postgresql.org/docs/8.2/static/bookindex.html -- F

Re: [GENERAL] keeping 3 tables in sync w/ each other

2007-09-18 Thread Filip Rembiałkowski
parent'; insert into child(data1) select 'this data was inserted to child'; select * from parent; select * from only parent; select * from child; alter table parent add column data2 text default 'new column default'; \d child -- Filip Rembiałkowski ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster

Re: [GENERAL] For index bloat: VACUUM ANALYZE vs REINDEX/CLUSTER

2007-09-18 Thread Filip Rembiałkowski
KB. ... and we have constant problem with index bloat and need to REINDEX frequently. the database is very redundant and has quite hight data retention rate (it's an ecommerce site) -- Filip Rembiałkowski ---(end of broadcast)--- TIP 3:

Re: [GENERAL] keeping 3 tables in sync w/ each other

2007-09-19 Thread Filip Rembiałkowski
> > => select * from child; > id | data1 > +--- > (0 rows) > Yes. You can however try SELECT FROM ... ONLY parent ... (that's what I used in example) and DELETE FROM ... ONLY parent ... -- Filip Rembiałkowski ---(end of broadcast)

Re: [GENERAL] Configuration starting point...

2007-09-19 Thread Filip Rembiałkowski
2007/9/19, Nathan Wilhelmi <[EMAIL PROTECTED]>: > Hello - Several people kindly responded directly to me with some > specific suggestions for this, however the message was mistakenly > deleted and not in the archives. If you remember who you are and > wouldn't mind resending that would be fantastic

Re: [GENERAL] Configuration starting point...

2007-09-19 Thread Filip Rembiałkowski
On 19/09/2007, Filip Rembiałkowski <[EMAIL PROTECTED]> wrote: > here you are, all posts from this thread are below. > > BTW, what happened to the archives? sorry nothing happened... I mixed it up :| Nathan, your post is there... http://archives.postgresql.org/pgsql-general/2007-

Re: [GENERAL] How to speedup intarray aggregate function?

2007-10-09 Thread Filip Rembiałkowski
this is possible in postgres 5. my results: your method (using intarray_push_array): 940 ms using array_cat: 860 ms same in PL/PgSQL: (LOOP, append) 800 ms same thing in Perl, no database (push array of arrays into one and print ): 18 ms cheers, Filip -- Filip Rembiałkowski ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/

Re: [GENERAL] XMIN semantic at peril ?

2007-10-11 Thread Filip Rembiałkowski
2007/10/11, Tom Lane <[EMAIL PROTECTED]>: > The only argument you cited that seems impressive to me is the one > about it being a Postgres-ism. Are you willing to have GNUmed tied > tightly to Postgres? Well, at least not in all aspects, if I may cut in. (from http://www.gnumed.org/) error ins

Re: [GENERAL] DB errors

2007-10-12 Thread Filip Rembiałkowski
12 07:40:16 postgres[30770]: [3-1] 2007-10-12 07:40:16 PDTERROR: could > not access status of transaction 2259991368 > Oct 12 07:40:16 postgres[30770]: [3-2] 2007-10-12 07:40:16 PDTDETAIL: Could > not read from file "pg_clog/086B" at offset 73728: Success. which ver

Re: [GENERAL] pg_dump - schema diff compatibility

2007-10-15 Thread Filip Rembiałkowski
is what I really want. > > Sorry for the confusing trail of thought and thanks for any comments, > t.n.a. > > ---(end of broadcast)--- > TIP 4: Have you searched our list archives? > >http://archives.postgresql.org/ > -- Fil

Re: [GENERAL] Resetting SEQUENCEs

2007-10-18 Thread Filip Rembiałkowski
l TEXT) RETURNS void as $$ BEGIN EXECUTE in_sql; RETURN; END; $BODY$ language plpgsql; select execute( $$select setval( '$$ || table_name || $$_id_seq', coalesce((select max(id) from $$ || table_name || $$),1), false ) $$ ) from information_schema.tables where you want;

Re: [GENERAL] print command in sproc

2007-10-30 Thread Filip Rembiałkowski
what you tell it to print :) Goboxe: The key question is, what you need 'print' for? For PL/PgSQL debugging, use RAISE NOTICE - or write your own logging functions If you are using psql, and just want to emit some static text - use '\echo' HTH. -- Filip Rembiałkowski --

Re: [GENERAL] Data cube in PostgreSQL

2007-10-30 Thread Filip Rembiałkowski
intained as such, but not > included into the 'core' project. They are certainly distributed along with > PostgreSQL in your packaging system of choice. > > Regards, > -- > dim > > ---(end of broadcast)--- &

Re: [GENERAL] Updated .vim file

2007-10-31 Thread Filip Rembiałkowski
s. dollar quoting is mostly used for function bodies. would you like to have them all in StringConstantColor? :) I like it more as it is now in Vim... -- Filip Rembiałkowski ---(end of broadcast)--- TIP 4: Have you searched our list ar

Re: [GENERAL] getting list of tables from command line

2007-10-31 Thread Filip Rembiałkowski
t a way to do that. for table in ` psql -qAtc "select table_name from information_schema.tables where table_schema='public'" ` -- Filip Rembiałkowski ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/

Re: [GENERAL] how can I shut off "more"?

2007-11-01 Thread Filip Rembiałkowski
ing? I tried > –echo-all, but that doesn't seem to do the trick. man psql search for 'pager' -- Filip Rembiałkowski ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subsc

Re: [GENERAL] Calculation for Max_FSM_pages : Any rules of thumb?

2007-11-02 Thread Filip Rembiałkowski
2007/11/2, Ow Mun Heng <[EMAIL PROTECTED]>: > pgstattuple.. Okay.. anyone have a centos rpm for it? Don't really have > access to a compiler on the (server) machine. don't you have postgresql-contrib package for centos? -- Filip Rembiałkowski --

Re: [GENERAL] Compressed Backup too big

2007-11-18 Thread Filip Rembiałkowski
ways* expect 10x ratio... it depends on data. Maybe usage patterns of your database changed and now the data is less compressable? -- Filip Rembiałkowski ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/

Re: [GENERAL] postgresql storage and performance questions

2007-11-19 Thread Filip Rembiałkowski
es on most machines) for each tuple so you will have about 27 + 1 + varchar data + numeric data per row, plus some overhaed for block headers ad.2 ) there is a null bitmap for each tuple which has nullable fields - so every 8 NULLable columns occupy one byte bitmap. PS. why do you post sam

Re: [GENERAL] postgresql storage and performance questions

2007-11-20 Thread Filip Rembiałkowski
- but I doubt it would speedup your queries twice, whatever they are. -- Filip Rembiałkowski ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster

Re: [GENERAL] postgres 8.3 beta 2 storage question

2007-11-29 Thread Filip Rembiałkowski
static/datatype-numeric.html#DATATYPE-NUMERIC-DECIMAL > 2. Does postgres always inserts a complete record into a page unlike oracle > whose record can span multiple pages? Does this waste space too? records larger than one page are TOASTed http://www.postgresql.org/docs/current/static/storag

Re: [GENERAL] Linux x Windows LOCALE/ENCODING compatibility

2010-11-01 Thread Filip Rembiałkowski
ble with all client encodings. you will get compatible bahaviour for your existing apps with SET client_encoding TO 'whatever'; /* this can be entered as pre-SQL on every client connection */ see http://www.postgresql.org/docs/8.4/static/multibyte.html, 22.2.3. Automatic Character

Re: [GENERAL] Why so many xlogs?

2010-11-01 Thread Filip Rembiałkowski
2010/11/1 hubert depesz lubaczewski : > as I understand, max number of xlog files in pg_xlog should be ( 1 + 2 * > checkpoint_segments ). why? for a server overloaded with R/W transactions, it's possible to go beyond this. checkpoints just do not keep up. right now I have an 8.3 with checkpoint_

Re: [GENERAL] JDBC Transactions

2010-11-01 Thread Filip Rembiałkowski
ork really well, with no locking at all.  You could read up on MVCC >> is you were interested. >> >> Without knowing what sql you are running, I can _totally guarantee_ it'll >> work perfectly with NO table locking.  :-) >> >> -Andy > &g

Re: [GENERAL] Group by and lmit

2010-11-03 Thread Filip Rembiałkowski
ect exec('create temp sequence tmpseq'||x) from (select distinct x from mytable) q; select x,y,counter from (select x, y, count(*) as counter from mytable group by x, y order by x, counter, y) subq where nextval(quote_ident('tmpseq'||x))<=5; -- Filip Rembiałkowski J

Re: [GENERAL] pg_dump order of rows

2010-12-11 Thread Filip Rembiałkowski
2010/12/11 jan > we are seeking for a solution to make the dumop (e.g. the backup) more > random > Can you please explain why? ( just curious, this seems a very strange requirement ) you _could_ _try_ to use postgres feature of synchronised seqscans to achieve this, but I'm not sure if it will

Re: [GENERAL] crosstab function

2010-12-14 Thread Filip Rembiałkowski
please show - source data structures (in form of CREATE TABLE please) - actual whole query that creates duplicates 2010/12/14 Sim Zacks > postgres 8.2.17 > > I am trying out the crosstab function (tablefunc contrib) for reporting > needs and I'm having a problem. > I have customers and pro

Re: [GENERAL] crosstab function

2010-12-14 Thread Filip Rembiałkowski
r by > productname') > as rpt(customername text,"ATX" int, > "CM-A510" int, > "CM-F82" int, > "CM-i586" int, > "CM-i686B" int, > "CM-i686M" int, > "CM-iAM" int, > "CM-iGLX" int, > &

Re: [GENERAL] Changing WAL Directory in Postgres

2010-12-15 Thread Filip Rembiałkowski
in short: sudo su - postgres pg_ctl stop -m fast cd $PGDATA mv pg_xlog /another/device ln -s /another/device/pg_xlog . pg_ctl start 2010/12/15 Adarsh Sharma > Hi all, > > From the last 2 days I am researching on Performance Tuning in Postgres. > > For this , I have to change my WAL directory t

Re: [GENERAL] Postgres Installation

2010-12-16 Thread Filip Rembiałkowski
2010/12/16 Adarsh Sharma > Dear all, > > Is there are any useful links for Installing Postgres_9.1 (recent version ) > in CentOS from its binaries. > > most recent stable version is 9.0.2. here you will find Yum instructions and links to RPM packages http://yum.pgrpms.org/howtoyum.php see also

Re: [GENERAL] Maximum size for char or varchar with limit

2010-12-20 Thread Filip Rembiałkowski
2010/12/19 Jasen Betts > I have some values of perhaps 20Mb that I might want to store samples > of in a partitioned table. (so I can delete them easily) what's the right > way? > > 20 Mbits or 20 MBytes? how big samples? answer to your question depends on what you want to do with these samples

Re: [GENERAL] How to use pgbouncer

2010-12-21 Thread Filip Rembiałkowski
2010/12/21 Adarsh Sharma : > Dear all, > > I am not able to find any useful document regarding Configuration and > Running Pgbouncer with Postgres-8.4.2. that's strange, there are several good pages on the web; there is also my mini-howto: http://filip.rembialkowski.net/pgbouncer-mini-howto-benchm

Re: [GENERAL] How to use pgbouncer

2010-12-21 Thread Filip Rembiałkowski
2010/12/21 Andreas Kretschmer : > > I'm looking for a solution to split read and write access to different > servers (streaming replication, you know ...). Can i do that with > pgpool? (setting backend_weightX=0 or 1)? I have read the doc, but i'm > not sure if pgpool the right solution, maybe you

Re: [GENERAL] Constraining overlapping date ranges

2010-12-22 Thread Filip Rembiałkowski
2010/12/21 McGehee, Robert : > PostgreSQLers, > I'm hoping for some help creating a constraint/key on a table such that there > are no overlapping ranges of dates for any id. > > Specifically: Using PostgreSQL 9.0.1, I'm creating a name-value pair table as > such this: > > CREATE TABLE tbl (id IN

Re: [GENERAL] Constraining overlapping date ranges

2010-12-22 Thread Filip Rembiałkowski
2010/12/22 Thomas Kellerer : > I'm curious why you use this syntax as you have fixed values and could use > the "standard" VALUES construct without problems: > > INSERT INTO tbl VALUES (1, '2010-01-01', '2010-12-31'); no particular reason; just two keystrokes less :-) The SQL code is postgres-sp

Re: [GENERAL] Constraining overlapping date ranges

2010-12-25 Thread Filip Rembiałkowski
; > Thanks, Robert > > PS. I don't think the f_point function is necessary. Something like: > ... EXCLUDE USING gist (id WITH =, f_period(start_date, end_date) WITH &&) > seems equivalent to your suggestion: > ... EXCLUDE USING gist (f_point(id) WITH ~=, f_period(s

Re: [GENERAL] WAL Archiving Stopped

2011-01-03 Thread Filip Rembiałkowski
archiver process will retry later; it never stops trying, sleep time is just longer. 2011/1/3, Norberto Delle : > Hi all > > I have a PostgreSQL 9.0.1 instance, with WAL Archiving. > Today, after some failed tries to archive a WAL file, it stopped trying > to archive the files, > but the number of

Re: [GENERAL] WAL Archiving Stopped

2011-01-04 Thread Filip Rembiałkowski
W dniu 4 stycznia 2011 14:55 użytkownik Norberto Delle napisał: > Em 3/1/2011 18:39, Filip Rembiałkowski escreveu: > > archiver process will retry later; it never stops trying, sleep time >> is just longer. >> >> 2011/1/3, Norberto Delle: >> >>> Hi all

Re: [GENERAL] How can I get the list of index(or other objects) which depends on a function

2011-01-05 Thread Filip Rembiałkowski
2011/1/5 flying eagle > I want to get all the dependencies of a table, I know how to get the index > list using sql, but I don't know how to get the list of objects who using a > function, for example: > > CREATE OR REPLACE FUNCTION reverse_last_64(TEXT) RETURNS TEXT AS $$ > SELECT > array_to_st

[GENERAL] nonexistent user in pg_class.relacl messing pg_dump output

2013-01-15 Thread Filip Rembiałkowski
Hi all, I observed this strange data error: In pg_dump output, I found such line, which is causing error upon restore: GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE addresshistory TO "158755274"; It appears that pg_class.relacl column has this literally typed in: dev=# select count(*) from pg_class

Re: [GENERAL] update performance of degenerate index

2013-01-28 Thread Filip Rembiałkowski
Just some questions, which might be helpful. What size is this index? What is underlying table size? Is ANALYZE running regularly (autovacuum or manual)? What are stats for exported_when column (pg_stats)? Did you look at pg_locks during this lengthy update? Do you have many concurrent statements

Re: [GENERAL] Why does slony use a cursor? Anyone know?

2013-03-07 Thread Filip Rembiałkowski
On Tue, Mar 5, 2013 at 3:51 PM, Shaun Thomas wrote: > Hey everyone, > > Frankly, I'm shocked at what I just found. > > We did a delete last night of a few million rows, and come back this > morning to find that slony is 9-hours behind. After some investigation, it > became apparent that slony open

[GENERAL] 9.0 hot standby, consistent recovery state question

2013-08-28 Thread Filip Rembiałkowski
Hi. I have Pg 9.0 wal shipping hot standby secondary server. The primary is under constant stream of updates (avg 20 TXID/s). There are many lengthy COPY FROM operations in the primary. After every restart of secondary postgres I observe that it takes a fair amount of time (sometimes few minute

Re: [GENERAL] Is autovacuum on?

2008-03-17 Thread Filip Rembiałkowski
um daemon is to periodically issue VACUUM and > ANALYZE commands - am I correct in thinking this implies that it will > not issue VACUUM FULL commands? Yes, you're correct. -- Filip Rembiałkowski -- 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] Which Python library - psycopg2 or pygresql?

2008-04-15 Thread Filip Rembiałkowski
me war! I just feel I need a bit > of knowledge-push to get me going. ;-) > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > -- Filip Rembiałkowsk

Re: [GENERAL] operator varchar = integer

2008-05-05 Thread Filip Rembiałkowski
k all queries and Datatypes. Also it works really fine and we all know > the risk of wrong auto casting. > > Anyone knows the Syntax? http://people.planetpostgresql.org/peter/index.php?/archives/18-Readding-implicit-casts-in-PostgreSQL-8.3.html PS. you could search archives or even use google. -- Filip Rembiałkowski

[GENERAL] Fwd: sql_type tinterval not supported by DBD::Pg

2008-05-21 Thread Filip Rembiałkowski
I would think that both INTERVAL => 1186 and TINTERVAL => 704 are valid values for bind_param pg_type hash $ perl -MDBD::Pg -le 'print $DBD::Pg::VERSION' 2.7.2 $ perl -MDBI -le 'print $DBI::VERSION' 1.604 Perl itself is v5.8.8 from Debian etch. -- Filip Rembiałkowski

Re: [GENERAL] Fwd: sql_type tinterval not supported by DBD::Pg

2008-05-23 Thread Filip Rembiałkowski
#x27;14 days', { 'pg_type' => 1186 } ) Cannot bind 6, sql_type interval not supported by DBD::Pg > >regards, tom lane > -- Filip Rembiałkowski

Re: [GENERAL] Fwd: sql_type tinterval not supported by DBD::Pg

2008-05-23 Thread Filip Rembiałkowski
Well, what can I say; thanks Radek. I am looking forward to see this fix included in stable DBD::Pg realease 2008/5/23 Radoslaw Zielinski <[EMAIL PROTECTED]>: > Filip Rembiałkowski <[EMAIL PROTECTED]> [23-05-2008 10:58]: > [...] > > the same with interval type: > [

Re: [GENERAL] dblink to non postgresql dbms

2008-06-29 Thread Filip Rembiałkowski
ite easy to rewrite. Think of DBD::ODBC or DBD::ADO (the latter works for me with ActivePerl) -- Filip Rembiałkowski -- 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] A question about inheritance and sequence

2009-06-24 Thread Filip Rembiałkowski
in the application or in trigger, you choose. Triggers are generally more robust. Quick example: NEW.noteid := SELECT 1+ COALESCE( (SELECT noteid FROM "Notes" WHERE userkey=NEW.userkey ORDER BY noteid DESC LIMIT 1), 0 ); Applications must take care of race conditions (like 2 cli

[GENERAL] just curious

2009-06-24 Thread Filip Rembiałkowski
http://wiki.postgresql.org/wiki/Identity_Guidelines say: "In Japan a turtle logo is used in place of the elephant." Why? -- Filip Rembiałkowski JID,mailto:filip.rembialkow...@gmail.com http://filip.rembialkowski.net/

Re: [GENERAL] Need suggestions

2009-06-26 Thread Filip Rembiałkowski
e on the partition. > I need to shut down all the databases to do mainatainence, right? > depends on the kind of maintenance. -- Filip Rembiałkowski JID,mailto:filip.rembialkow...@gmail.com http://filip.rembialkowski.net/

Re: [GENERAL] PREPARE query with IN?

2009-08-05 Thread Filip Rembiałkowski
w.postgresql.org/docs/8.3/interactive/sql-prepare.html since > I'm probably not the first one to ask about it :) > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgs

Re: [GENERAL] explicit JOIN faster than implicit?

2009-08-05 Thread Filip Rembiałkowski
n analyze of both queries, before and after VACUM ANALYZE of all used tables. You can also try to increase statistics target for join columns. Indexes might help too. -- Filip Rembiałkowski JID,mailto:filip.rembialkow...@gmail.com http://filip.rembialkowski.net/

Re: [GENERAL] duplicate key violates unique constraint

2009-08-08 Thread Filip Rembiałkowski
36, > 'public.t_i_shift_shadow', 't_i_shift_shadow_pkey', 'shift shadow > table'); - ERROR: duplicate key violates unique constraint > "sl_table_tab_reloid_key" > Looks like you are trying to add a table with same ID second time. Table ID has to

Re: [GENERAL] New database or New Schema?

2009-08-25 Thread Filip Rembiałkowski
n. And finally: Postgres is a full-featured relational database with focus on ACID. Does JMS actually need this? If it supports backend pairing (for durability), maybe it's more efficient to use a pair of hsqldbs or mysqls. I'd suggest some performance testing here. So, depending on

Re: [GENERAL] Fail to start Postgres on Ubuntu kernel 2.6.28-15-generic

2009-09-08 Thread Filip Rembiałkowski
guration. > <== end of log > > As a workaround it's possible to boot into the "old" kernel. > > quite a common problem... see http://www.postgresql.org/docs/8.3/static/kernel-resources.html obvious question: what's in /proc/sys/kernel/shmmax ? maybe your new kernel does not read sysctl settings properly? -- Filip Rembiałkowski JID,mailto:filip.rembialkow...@gmail.com http://filip.rembialkowski.net/

Re: [GENERAL] sync structures

2009-09-28 Thread Filip Rembiałkowski
As. pg_dump -sOx dev_db_name > dev.schema pg_dump -sOx prod_db_name > prod.schema diff -u dev.schema prod.schema -- Filip Rembiałkowski JID,mailto:filip.rembialkow...@gmail.com http://filip.rembialkowski.net/

Re: [GENERAL] sync structures

2009-09-28 Thread Filip Rembiałkowski
tools for db comparing but they are not perfect too. BTW, you did not specify what exactly did not work when you tried apgdiff. this would help others to help you. -- Filip Rembiałkowski JID,mailto:filip.rembialkow...@gmail.com http://filip.rembialkowski.net/

Re: [GENERAL] Upgrade db format without older version of PostgreSQL

2009-09-29 Thread Filip Rembiałkowski
;d rather try this: compile 7.2 from source, bring up a temporary postgres 7.2 instance, dump it (using pg_dumpall from 8.3), and load the dump into 8.3. -- Filip Rembiałkowski JID,mailto:filip.rembialkow...@gmail.com http://filip.rembialkowski.net/

Re: [GENERAL] How useful is the money datatype?

2009-10-03 Thread Filip Rembiałkowski
I understand it's kind of a survey, so to answer the question from my point of view: The "money" data type is not useful at all. -- Filip Rembiałkowski JID,mailto:filip.rembialkow...@gmail.com http://filip.rembialkowski.net/

Re: [GENERAL] Wishlist of PL/Perl Enhancements for PostgreSQL 8.5

2009-10-06 Thread Filip Rembiałkowski
> If you want inter-function calls you use internal_foo. > > this really would be a great feature for many plperl users. ( I'm not sure how it breaks current PL model in postgres) -- Filip Rembiałkowski JID,mailto:filip.rembialkow...@gmail.com http://filip.rembialkowski.net/

Re: [GENERAL] Delete fails with out of memory

2009-10-18 Thread Filip Rembiałkowski
ee (0 > chunks); 744 used >pg_conversion_name_nsp_index: 1024 total in 1 blocks; 280 free (0 > chunks); 744 used > MdSmgr: 8192 total in 1 blocks; 5200 free (0 chunks); 2992 used > LOCALLOCK hash: 8192 total in 1 blocks; 3912 free (0 chunks); 4280 used > Timezo

Re: [GENERAL] Help - corruption issue?

2011-04-18 Thread Filip Rembiałkowski
Phoenix, how large (in total) is this database)? can you copy (cp -a) the data directory somewhere? I would do this just in case :-) regarding the manual recovery process: 1. you'll have to isolate corrupted table. you can do this by dumping all tables one-by-one (pg_dump -t TABLE) until you g

Re: [GENERAL] pg_dump compress

2011-09-23 Thread Filip Rembiałkowski
2011/9/23 Gregg Jaskiewicz > can you pipe things on windows ? > Yes you can. It surprised me positively several years ago. http://www.microsoft.com/resources/documentation/windows/xp/all/proddocs/en-us/redirection.mspx?mfr=true > It's a desktop system after all, :-) what a nice dose of condesc

Re: [GENERAL] Does postgresql 9.0.4 use index on PREFIX%SUFFIX queries?

2011-09-26 Thread Filip Rembiałkowski
Edson, 1. PostgreSQL IS able to use btree index to execute this query. More generally, it is able to use btree index for all PREFIX search. 2. You will need a special (NOT spatial) index for it CREATE INDEX notafiscal_numeroctc_tpo_idx ON notafiscal (numeroctc text_pattern_ops); ( see http://www.

Re: [GENERAL] [Solved] Generic logging system for pre-hstore using plperl triggers

2011-09-26 Thread Filip Rembiałkowski
2011/9/27 Diego Augusto Molina > Hi, I had to implement a logging system for some DBs in work. It's > generic and implemented using plperl. I've seen tons of mails on the > list from newbies asking for something like this using plpgsql, but no > specific solution is pointed for them. I think this

Re: [GENERAL] Quick-and-Dirty Data Entry with LibreOffice3?

2011-09-26 Thread Filip Rembiałkowski
2011/9/26 Rich Shepard > Rather than writing an application right now to enter data into a table I > thought of trying LibreOffice as a front end. But, it doesn't seem to work > as OO.o did. It does, albeit you will need libreoffice-base which is not always installed by default (not in my Ubun

Re: [GENERAL] Help needed in Search

2011-09-29 Thread Filip Rembiałkowski
Siva, in addition to what others said, please note that underscore matches any character. to change it use escape char. http://www.postgresql.org/docs/9.1/static/functions-matching.html#FUNCTIONS-LIKE 2011/9/28 Siva Palanisamy > Hi All, > > ** ** > > I am trying to retrieve the contact

Re: [GENERAL] gaps/overlaps in a time table : current and previous row question

2011-10-05 Thread Filip Rembiałkowski
2011/10/5 thomas veymont > hello, > > let's say that each rows in a table contains a start time and a end > time ("timeinterval" type), > there is no such type ( no result for select * from pg_type where typname ~ 'timeinterval' ). can you show exact table structure (output of psql "\d" or bette

Re: [GENERAL] Restoring 2 Tables From All Databases Backup

2011-10-05 Thread Filip Rembiałkowski
2011/10/5 Adarsh Sharma > Dear all, > > About 1 month ago, I take a complete databases backup of my Database server > through pg_dumpall command. > Today I need to extract or restore only 2 tables in a database. > > Is it possible or I have to restore complete Databases again. Size of > backup is

Re: [GENERAL] How to create database link and synonym in postgresql 9.0

2011-10-05 Thread Filip Rembiałkowski
There is no CREATE SYNONYM in PostgreSQL and it's not planned for implementation. There is also no direct support for foreign data wrapper. But it's planned for 9.2. Nearest that you can get with PostgreSQL 9.0 is cautious use of dblink and views and rules. here's a sample script to show what I

Re: [GENERAL] Restoring 2 Tables From All Databases Backup

2011-10-06 Thread Filip Rembiałkowski
2011/10/6 Adarsh Sharma > ** > Thanks to all, the problem is solved now. > > But Still I donot know how to use existing data directory (near about > 110GB) in a new Postgres Installation. > I ask this in the list yesterday but still no clue on this. > Please guide if it is possible. > > You need

Re: [GENERAL] Strange primary key error on insertion

2011-10-06 Thread Filip Rembiałkowski
2011/10/6 Rory Campbell-Lange > I have a strange issue (postgres 8.4) trying to insert old rows back > into the s_tbls table. A check on the primary key (n_id) between > s_tbl_import and s_tbls yields no matches, yet the index on s_tbls.n_id > yields an error when attempting to insert: > > => sel

Re: [GENERAL] Restoring 2 Tables From All Databases Backup

2011-10-06 Thread Filip Rembiałkowski
tgresql-8.4 and Ubuntu 10.04 > > What to do know? > > Thanks > > > Raymond O'Donnell wrote: > > On 06/10/2011 11:34, Filip Rembiałkowski wrote: > > > > To use existing data directory in new installation, you can just stop > the server, replace data_director

Re: [GENERAL] Best PostGIS function for finding the nearest line segment to a given point

2011-10-08 Thread Filip Rembiałkowski
2011/10/8 René Fournier > Wow, have to say, I love Postgresql and PostGIS. Just awesome. > > So I have a table with ~400,000 rows, each representing a road or street > (multi line segment). I want to select the row whose line segment is closest > the a given point. The following query... > > gc3=

Re: [GENERAL] Best PostGIS function for finding the nearest line segment to a given point

2011-10-09 Thread Filip Rembiałkowski
2011/10/8 René Fournier > > Thanks. Based on some further reading, this is what I came up with, in > order to hopefully use the GiST index to greatest benefit: > > gc3=# SELECT datasetnam, r_hnumf, r_hnuml, r_stname_c, r_placenam, > ST_Distance(ST_GeomFromText('POINT(-114.053205 51.069644)',4269)

Re: [GENERAL] could not create file "base/16384/11500": File exists

2011-10-10 Thread Filip Rembiałkowski
Hello, You gave much too little information. Please see http://wiki.postgresql.org/wiki/Guide_to_reporting_problems Especially: - server version, platform - vacuum-related settings It *might* be OID wraparaound. 2011/10/10 Harshitha S > Hi, > > We are the following msg in the postgres logs.

Re: [GENERAL] could not create file "base/16384/11500": File exists

2011-10-10 Thread Filip Rembiałkowski
is 9.0.4 on the Linux platform. >> The vaccum - related settings have default values. >> >> If it is OID wrap around, how to resolve it? >> >> Regards, >> Harshitha >> >> >> >> 2011/10/10 Filip Rembiałkowski >> >>> Hello, >&g

Re: [GENERAL] Drill-downs and OLAP type data

2011-10-11 Thread Filip Rembiałkowski
Mondrian (which is a part of Pentaho BI stack) is an open source OLAP engine with MDX. See http://community.pentaho.com/projects/bi_platform/ 2011/10/12 Anthony Presley > Hi there! > > We have a typical data-warehouse type application, and we'd like to set up > a star-schema type data analysis

Re: [GENERAL] 9.1 got really fast ;)

2011-10-16 Thread Filip Rembiałkowski
2011/10/15 Chris Travers > > > On Sat, Oct 15, 2011 at 1:33 PM, Grzegorz Jaskiewicz wrote: > >> >> On 15 Oct 2011, at 21:20, Thomas Kellerer wrote: >> > >> > Total runtime: -2.368 ms << this is amazing ;) >> > >> > This is with 9.1.1 on a Windows XP machine >> >> Are you saying that Windows

Re: [GENERAL] Improve MMO Game Performance

2012-10-13 Thread Filip Rembiałkowski
On Sat, Oct 13, 2012 at 1:52 PM, Arvind Singh wrote: > > To understand my query, please consider the following scenario > we store game progress in a postgres table. > A tournament starts with four players and following activity > > Each player starts with 100hitpoints > player 1 makes a strike (

[GENERAL] question on foreign key lock

2012-11-01 Thread Filip Rembiałkowski
Hello. Why adding FK creates AccessExclusiveLock on referenced tabble? {{{ CREATE TABLE A ( id integer, idb integer ); INSERT INTO A (id,idb) SELECT x, x%4 FROM generate_series(1,100) x; CREATE TABLE B ( id int primary key ); INSERT INTO B VALUES (0),(1),(2),(3); BEGIN; ALTER TABLE A ADD CONST

[GENERAL] lc_ctype does not work on windows ?

2010-04-27 Thread Filip Rembiałkowski
23 encoding | 6 datcollate| Polish, Poland datctype | Polish, Poland datistemplate | f datallowconn | t datconnlimit | -1 datlastsysoid | 11563 datfrozenxid | 649 dattablespace | 1663 datconfig | datacl| -- Filip Rembiałkowski JID,mailto:filip.rembialkow...@g

Re: [GENERAL] Indexing queries with bit masks

2010-05-01 Thread Filip Rembiałkowski
2010/4/30 Mike Christensen : > Ok I've been blatantly lying, err, purposely simplifying the problem for the > sake of the original email :) > > I've read over the responses, and am actually now considering just not using > any index at all.  Here's why: > > First, this actually isn't the only thing

Re: [GENERAL] Formatted reports

2010-05-07 Thread Filip Rembiałkowski
2010/5/7 Sorin Schwimmer : > Hi All, > > Is there in PostgreSQL an equivalent of Oracle's BREAK/COMPUTE reporting > feature? No, there isn't. You need some procedural language function, or use external tool to accomplish this. There are free reporting engines. jaspersoft, pentaho, BIRT, ... [[

  1   2   3   >