[GENERAL] dbi-link freezing up DBs, needing reboot
I was playing around with dbi-link, hoping to get it connected to a teradata database. However, before I dive into that, I figured that I might as well try it out first on a PG Database (on another server) So, it installed dbi-link fine. I did a select on a 30GB table and it froze the Originating database and it ALSO froze the foreign database. Looking into the foreign box's logs, I see for some reason the network just ceased to function? (can't be a coincidence?) Aug 30 15:15:18 kernel: NETDEV WATCHDOG: eth0: transmit timed out Aug 30 15:15:18 kernel: r8169: eth0: link up I then had to reboot the originating DB/box. Once it got back up, I did a select on a very small table <1MB and the data is returned properly and in timely fashion. Then I tried it on a slightly bigger table --> 50MB and it froze again select * from xmms_b4.log_update where record_update_date_time > '2009-08-30 10:00:00' and record_update_date_time < '2009-08-30 11:00:00' limit 10; NOTICE: SELECT dbi_link.cache_connection( 2 ) at line 12. #\d log_update; Column |Type | Modifiers -+-+ job_name| text| not null table_name | text| not null from_date | timestamp without time zone | not null to_date | timestamp without time zone | rows_deleted| integer | delete_duration | interval| rows_inserted | integer | insert_duration | interval| rows_updated| integer | update_duration | interval| record_update_date_time | timestamp without time zone | not null default now() After 2 times of this happening, I'm really worried that it will do other nasty things. Help? PS : dbi-link is, for me, ultimately to try if I can get it to connect to teradata to pull some data on a daily basis. I currently use dblink for pg-to-pg connections -- 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] Snow Leopard and PG 8.4 problems....
Hi Jerry, I have been able to use just the ip in the host field but Snow Leopard seems to have broken using a 'name' in the host field... see below. It appears that compiling pg 8.4 gives rise to libs with architecture of x386_64... hm, i have no problems to compile postgresql on snow leopard. all linking defaults to x86_64-architecture on my machine. you have probably installed macports or fink and build 32-libs on mac os x 10.5 before you upgraded to snow leopard? try removing all references to these 3rd-party libs and try compile again. What sort of magic do I have to use to get a 32 bit version of the various libs ? ( I don't seem to be able to find where in the make file I can ask the linker to add the various archs, but I am getting bleary eyed.) I don't seem to be able to compile python support against the apple 2.6 or the macpython 2.5 versions (of python). it works out of the box on my machine: ./configure --enable-python python 2.6 (delivered in snow leopard) is used and compiles without any warning or error. Has anyone built a Snow Leopard version of pg with python support? yes. Am I the only one to have problems with dblink on Snow Leopard? i dont use dblink so i cant say if it is broken on my machine too. regards, jan otto -- 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] best practise/pattern for large OR / LIKE searches
On 2009-08-26, Ries van Twisk wrote:
>
> --Apple-Mail-1173-222712773
> Content-Type: text/plain;
> charset=US-ASCII;
> format=flowed;
> delsp=yes
> Content-Transfer-Encoding: 7bit
>
> Hey All,
>
> I am wondering if there is a common pattern for these sort of queries :
>
> SELECT * FROM tbl WHERE datanumber LIKE '%12345%' OR LIKE '%54321%' OR
> LIKE '%8766%' OR LIKE '%009%', ..
SELECT * FROM tbl WHERE datanumber LIKE ANY
ARRAY('%12345%','%54321%','%8766%'...)
> The number of OR/LIKES are in the order of 50-100 items...
> the table tbl is a couple of million rows.
regex might perfrom better than LIKE ANY
SELECT * FROM tbl WHERE '12345|54321|8766|009' ~ datanumber;
regex is compiled to a finite state machine and then the datanumber
column is scanned in a single pass (for each row)
> Searches are currently taking to long and we would like to optimize
> them, but before we dive into our own solution we
> where wondering if there already common solutions for this...
try regex first if that's too slow you may need to write a
dictionary function that splits datanuimber into it's components
and use full text index/search. (this will slow down updates as they will do
upto 20 inserts into the index)
searches should then be optimally fast
--
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] New database or New Schema?
""Filip Rembiałkowski"" wrote in message news:92869e660908250856g62561141n557f88015cd21...@mail.gmail.com... 2009/8/25 Eric Comeau "David Fetter" wrote in message news:20090821170259.ga6...@fetter.org... > On Fri, Aug 21, 2009 at 12:16:45PM -0400, Eric Comeau wrote: >> In the next release of our software the developers are moving to >> JBoss and have introduced the use of JBoss Messaging. They want to >> change from using the built-in hsqldb to using our PostgreSQL >> database. >> >> What is the best approach, create a new database or new schema >> with-in our current PostgreSQL database? >> >> I'm leaning toward creating a new schema as we already have built-in >> procedures to backup and restore the application database. > > It's difficult to tell from what you've described. If you were even > vaguely contemplating queries that touched both that database and > others you already have, it becomes much easier: use a schema. So to be so vague I was trying to keep the description short. I don't see that we will need to query data between the systems for any business type of operations. Potentially for troubleshooting. I was hoping to get more input on what the best-practices (benefits/disadvantages) of going with a new schema versus a new database. I don't really see any downside with going with a new schema in the application database, and since we already have built-in procedures (at the application layer) to back-up and restore the application database (using pg_dump) I don't believe they will need to change. Adding a new database, I need to update the backup process to either dump a second db (jboss_jms) or start using pg_dumpall. New schema approach is generally OK. But: Take a closer look at usage patterns of this Java messaging system. I mean, the way it uses Postgres database. Do some testing. Does it work by holding long-open transactions (I've seen such setups of java based queueing services)? Does it run huge amount of UPDATE/INSERT/DELETE queries? These can be a PITA. For example, they can make autovacuum daemon useless, or - in some extremal setups - they could cause transaction ID wraparound issues. They can also hurt if you are going to use trigger-based replication. 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 your real requirements and usage scenario, you have choices ranging from extra schema to separate postgres instance to using something else - because it's simply a better tool for the job. HTH. Thanks Filip id does, you bring up some very important issues to consider that I have run into in the "wild" with our own product. I'll send a note off to the "PM" types that we need to allocate some more testing for this to the project plan. The problem is, they won't and unless I do it on my own, when it hits the wild and causes an issue I'll be the one holding the bag fixing the bloated DB with the customer. Eric
[GENERAL] Trouble using TG_TABLE_NAME in BEFORE INSERT OR UPDATE trigger
Using PostgreSQL 8.4.0, I have the following generic trigger defined for use by a collection of the same structured tables: CREATE OR REPLACE FUNCTION pdf_active_check() RETURNS trigger AS $BODY$ DECLARE var_active BOOLEAN; BEGIN SELECT p.active INTO STRICT var_active FROM TG_TABLE_NAME pv INNER JOIN pdfs p ON p.id = pv.pdf_id; IF active THEN RETURN NEW; ELSE RETURN NULL; END IF; END; $BODY$ LANGUAGE 'plpgsql'; ERROR: syntax error at or near "$1" LINE 1: SELECT p.active FROM $1 pv INNER JOIN pdfs p ON p.id = pv ^ QUERY: SELECT p.active FROM $1 pv INNER JOIN pdfs p ON p.id = pv.pdf_id CONTEXT: SQL statement in PL/PgSQL function "pdf_active_check" near line 4 When I try this kind of substitution with TEXT type variables, there isn't any problem. I can imagine it has something to do with TG_TABLE_NAME being of type NAME, but I can't find anything in the manual about the difference or how to convert. Google wasn't much help either, but I figured this is fairly trivial for those on this list... Stephen Cuppett steve at cuppett dot com
Re: [GENERAL] export a schema / import as new schema
On 2009-08-21, David Kerr wrote:
> On Fri, Aug 21, 2009 at 12:00:11PM -0700, Joshua D. Drake wrote:
> - On Fri, 2009-08-21 at 11:56 -0700, David Kerr wrote:
> - > Is there an easy way, that I'm missing, where I can export a schema from
> - > database A and then rename it on load into database B?
> -
> - pg_dump -s foo|psql bar
>
> Sorry, I wasn't clear.
>
> What I have is:
>
> [Database 1].[Schema 1]->[Data Set 1]
> [Database 2].[Schema 1]->[Data Set 2]
>
> What I want to do is:
>
> Export [Database 1].[Schema 1]->[Data Set 1]
> Import [Database 2].[Schema 2]->[Data Set 1]
>
> Leaving me with
> [Database 2].[Schema 1]-[Data Set 2]
>[Schema 2]-[Data Set 1]
>
> So that i can now compare Data Set 1 and Data Set 2
>
> Currently, I'm creating the new schema in database 1 and then exporting that
> data
> into database 2, but i was hoping there was a better way.
you just need a little sed magic, (untested)
pg_dump -s schema1 database1 |
sed '/^COPY/ /\\./ { p;d } ;
s/^SET search_path = schema1,/^SET search_path = schema2,/;
s/ schema1\./ schema2\./;
s/ SCHEMA schema1 / SCHEMA schema2 /;
'|
psql database2
if you need mixed case,spaces,punctuation,etc in the names it's a
little harder
--
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] Removing older versions
On 2009-08-29, Blueb wrote: > I installed PostgreSQL 8.4 over a PostgreSQL 8.3 installation (using msi) on > a Windows machine. there's a msi installer for 8.4? > My pgAdmin3 program correctly displays only PostgreSQL 8.4 on localhost. > > I went to my harddrive and noticed that I have PostgreSQL 8.3 "DATA" files > installed in: C:\Program Files\PostgreSQL\8.3 > > Thinking these were simply empty folders.. I tried to delete them, but I get > an error that I needed permission to do this. > > I looked and there seems to be 13 megs of data files that the install > program did not delete. > > How can I do this from either pgAdmin.exe or manually? you need windows superuser - try doing it as administrator. ensure that the pgsql-8.3 service is not running, or try this: runas postgres cmd /c del /s /y C:\Program Files\PostgreSQL\8.3 -- 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] Select data for current week only
On 2009-08-27, BlackMage wrote: > > Hey all, > > I've been trying to figure this problem out with just using sql but I'm not > sure how too. I have a table that holds events for each week but I only want > to select events happening for the current week(Mon-Sun). So can anyone help > me out with this just using sql? I've accomplished it sorta using php but > only within the current 7 day range(example Thursday-Thursday) but not by > the week. > > The field I am using for sorting is a Date type with the format -mm-dd > hh:mm:ss . sounds like a timestamp. case it to date yourfield :: date between 'today' :: date - extract (DOW from 'today' :: date ) and 'today' :: date - extract (DOW from 'today' :: date ) + 6 -- 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] Trouble using TG_TABLE_NAME in BEFORE INSERT OR UPDATE trigger
Hello you cannot use variable as table or column name in direct query. look on execute statemen: http://www.postgresql.org/docs/8.4/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN or don't use generic triggers. regards Pavel Stehule 2009/8/30 Stephen Cuppett : > Using PostgreSQL 8.4.0, I have the following generic trigger defined for use > by a collection of the same structured tables: > > CREATE OR REPLACE FUNCTION pdf_active_check() RETURNS trigger AS $BODY$ > DECLARE > var_active BOOLEAN; > BEGIN > SELECT p.active INTO STRICT var_active FROM TG_TABLE_NAME pv INNER > JOIN pdfs p ON p.id = pv.pdf_id; > IF active THEN > RETURN NEW; > ELSE > RETURN NULL; > END IF; > END; > $BODY$ LANGUAGE 'plpgsql'; > > ERROR: syntax error at or near "$1" > LINE 1: SELECT p.active FROM $1 pv INNER JOIN pdfs p ON p.id = pv > ^ > QUERY: SELECT p.active FROM $1 pv INNER JOIN pdfs p ON p.id = pv.pdf_id > CONTEXT: SQL statement in PL/PgSQL function "pdf_active_check" near line 4 > > When I try this kind of substitution with TEXT type variables, there isn't > any problem. I can imagine it has something to do with TG_TABLE_NAME being > of type NAME, but I can't find anything in the manual about the difference > or how to convert. Google wasn't much help either, but I figured this is > fairly trivial for those on this list... > > Stephen Cuppett > steve at cuppett dot com > > -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] best practise/pattern for large OR / LIKE searches
Hello
> regex is compiled to a finite state machine and then the datanumber
> column is scanned in a single pass (for each row)
>
>> Searches are currently taking to long and we would like to optimize
>> them, but before we dive into our own solution we
>> where wondering if there already common solutions for this...
>
> try regex first if that's too slow you may need to write a
> dictionary function that splits datanuimber into it's components
> and use full text index/search. (this will slow down updates as they will do
> upto 20 inserts into the index)
>
> searches should then be optimally fast
>
I did some tests:
1) I fill test table
insert into test SELECT
array_to_string(array_agg(array_to_string(ARRAY(select
substring('01234567890' from (random()*10)::int + 1 for 1) from
generate_series(1,(random()*10+5)::int + i - i)),'')),',') as b from
generate_series(1,10) g(i) group by (random()*1000)::int;
2. I tested searching of 5 or 13 values. I did tests on 8.4 and 8.1
8.1
using like 190ms(440ms*)
using regexp 115ms(259ms*)
* for 13 values - so there regexp is faster than like
on 8.4
using like 80ms(151ms)
using regexp 131ms(267ms)
so like is faster then regexp on 8.4.
fulltext test (8.4)
420ms(470ms) -- without index
14ms(26ms) -- with GiST index
1ms(2ms) -- with Gin index
some samples of test queries:
select * from test where to_tsvector('simple',a) @@
to_tsquery('simple','296426496|7707431116555|98173598191|302598|53174827|02292064629|188631468777|4756243248|920473|16602317|76613513|78640|9176323');
select * from test where a ~
'296426496|7707431116555|98173598191|302598|53174827|02292064629|188631468777|4756243248|920473|16602317|76613513|78640|9176323';
select * from test where a like '%296426496%' or a like '%
7707431116555%' or a like '%98173598191%' or a like '%302598%' or a
like '%53174827%' or a like '%02292064629%' or a like '%188631468777%'
or a like '%4756243248%' or a like '%920473%' or a like '%16602317%'
or a like '%76613513%' or a like '%78640%' or a like '%9176323%';
regards
Pavel Stehule
--
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] Trouble using TG_TABLE_NAME in BEFORE INSERT OR UPDATE trigger
Sorry, found my answer wrt "dynamic queries", etc. Restructured trigger to look like this: CREATE OR REPLACE FUNCTION pdf_active_check() RETURNS trigger AS $BODY$ DECLARE var_curs1 refcursor; var_active BOOLEAN; BEGIN open var_curs1 FOR EXECUTE 'SELECT p.active FROM '||TG_TABLE_NAME||' pv INNER JOIN pdfs p ON p.id = pv.pdf_id WHERE p.active = true'; FETCH var_curs1 INTO var_active; IF FOUND THEN RETURN NEW; ELSE RETURN NULL; END IF; END; $BODY$ LANGUAGE 'plpgsql'; Sorry for the noise. Stephen Cuppett steve at cuppett dot com On Sun, Aug 30, 2009 at 8:11 AM, Stephen Cuppett wrote: > Using PostgreSQL 8.4.0, I have the following generic trigger defined for > use by a collection of the same structured tables: > > CREATE OR REPLACE FUNCTION pdf_active_check() RETURNS trigger AS $BODY$ > DECLARE > var_active BOOLEAN; > BEGIN > SELECT p.active INTO STRICT var_active FROM TG_TABLE_NAME pv INNER > JOIN pdfs p ON p.id = pv.pdf_id; > IF active THEN > RETURN NEW; > ELSE > RETURN NULL; > END IF; > END; > $BODY$ LANGUAGE 'plpgsql'; > > ERROR: syntax error at or near "$1" > LINE 1: SELECT p.active FROM $1 pv INNER JOIN pdfs p ON p.id = pv > ^ > QUERY: SELECT p.active FROM $1 pv INNER JOIN pdfs p ON p.id = pv.pdf_id > CONTEXT: SQL statement in PL/PgSQL function "pdf_active_check" near line 4 > > When I try this kind of substitution with TEXT type variables, there isn't > any problem. I can imagine it has something to do with TG_TABLE_NAME being > of type NAME, but I can't find anything in the manual about the difference > or how to convert. Google wasn't much help either, but I figured this is > fairly trivial for those on this list... > > Stephen Cuppett > steve at cuppett dot com > >
Re: [GENERAL] Trouble using TG_TABLE_NAME in BEFORE INSERT OR UPDATE trigger
On Sun, Aug 30, 2009 at 03:22:16PM +0200, Pavel Stehule wrote: > Hello > > you cannot use variable as table or column name in direct query. > > look on execute statemen: > http://www.postgresql.org/docs/8.4/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN > > or don't use generic triggers. You know, it would be really nice to have a way of dealing with this. I was thinking, just like we have the OPERATOR() keyword for tokens that wouldn't normally be considered operators, we could use TABLE() to introduce table name where they wouldn't normally be recognised. Like: SELECT a FROM TABLE($1); TABLE is a reserved word, so there doesn't appear to be a backward compatability problem. The argument would have to be a text constant, or a bind parameter. Sure, it would disable query caching, but the benefits in clarity seem worth it. Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > Please line up in a tree and maintain the heap invariant while > boarding. Thank you for flying nlogn airlines. signature.asc Description: Digital signature
Re: [GENERAL] Trouble using TG_TABLE_NAME in BEFORE INSERT OR UPDATE trigger
2009/8/30 Martijn van Oosterhout : > On Sun, Aug 30, 2009 at 03:22:16PM +0200, Pavel Stehule wrote: >> Hello >> >> you cannot use variable as table or column name in direct query. >> >> look on execute statemen: >> http://www.postgresql.org/docs/8.4/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN >> >> or don't use generic triggers. > > You know, it would be really nice to have a way of dealing with this. I > was thinking, just like we have the OPERATOR() keyword for tokens that > wouldn't normally be considered operators, we could use TABLE() to > introduce table name where they wouldn't normally be recognised. Like: > > SELECT a FROM TABLE($1); > > TABLE is a reserved word, so there doesn't appear to be a backward > compatability problem. The argument would have to be a text constant, > or a bind parameter. Sure, it would disable query caching, but the > benefits in clarity seem worth it. > > Have a nice day, The core of this topic is plan and syntax query analysis - different tables has different columns and indexes. So plan should be different and some queries should be correct (or not) for different tables. Theoretically we can define some "operator" table for plpgsql. This operator ensure so query will be dynamic. But I am against. It add to plpgsql some new alchemy. Now the plpgsql block schema is very clear and protect young programmers against some basic error. Dynamic queries should be slower - or faster. It is best when any developer have to understand to differences between query and dynamic query early - lot of magical things in plpgsql will be clean. I disliked dynamic sql too. But with USING clause the working with it is comfortable and clean: sample: EXECUTE 'SELECT * FROM ' || tabname::regclass || WHERE col=$1' USING var; older variant EXECUTE 'SELECT * FROM ' || quote_iden(tabname) || 'WHERE col='|| quote_literal(var) ... regards Pavel Stehule > -- > Martijn van Oosterhout http://svana.org/kleptog/ >> Please line up in a tree and maintain the heap invariant while >> boarding. Thank you for flying nlogn airlines. > > -BEGIN PGP SIGNATURE- > Version: GnuPG v1.4.9 (GNU/Linux) > > iD8DBQFKmoChIB7bNG8LQkwRAhd1AJ9HevaBdodmpJ1sKSOjyr+70d25cACfRRpl > KDDTg2K8xopGGBIh8A1pBtg= > =/dQN > -END PGP SIGNATURE- > > -- 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] dbi-link freezing up DBs, needing reboot
Ow Mun Heng wrote: I was playing around with dbi-link, hoping to get it connected to a teradata database. However, before I dive into that, I figured that I might as well try it out first on a PG Database (on another server) So, it installed dbi-link fine. I did a select on a 30GB table and it froze the Originating database and it ALSO froze the foreign database. Looking into the foreign box's logs, I see for some reason the network just ceased to function? (can't be a coincidence?) Aug 30 15:15:18 kernel: NETDEV WATCHDOG: eth0: transmit timed out Aug 30 15:15:18 kernel: r8169: eth0: link up That looks like it came from dmesg. Did you look in the postgres log? "froze" is not a helpful description. PG spawns off a client for each connection, and I doubt one client could freeze another. So was the one connection froze, all PG clients froze, or the entire computer froze? You said you had to reboot, so I assume the entire computer. On the foreign box, have you ever pushed a large amount of data over the network? You might wanna try to copy some really big files a few times and see if you get the eth0 timeout error again. I assume you are using Linux and a new version of PG, right? -Andy -- 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] dbi-link freezing up DBs, needing reboot
-Original Message- From: Andy Colson [mailto:a...@squeakycode.net] Ow Mun Heng wrote: >> I was playing around with dbi-link, hoping to get it connected to a >teradata >> database. However, before I dive into that, I figured that I might as >well >> try it out first on a PG Database (on another server) >> >> I did a select on a 30GB table and it froze the Originating database and >it >> ALSO froze the foreign database. >> >That looks like it came from dmesg. Did you look in the postgres log? > >"froze" is not a helpful description. PG spawns off a client for each >connection, and I doubt one client could freeze another. So was the one >connection froze, all PG clients froze, or the entire computer froze? > >You said you had to reboot, so I assume the entire computer. > >On the foreign box, have you ever pushed a large amount of data over the >network? You might wanna try to copy some really big files a few times and >see if you get the eth0 timeout error again. > >I assume you are using Linux and a new version of PG, right? Sorry, I don't know how else to describe it cos I don't much activity over my ssh connections. Even top refused to work on the foreign box. Yeah, the foreign box has handled large amount of data before. I pushed out over 300G of data while rsyncing the db to another slave. Centos -5.2 and PG 8.3.7 on the foreign box and 8.3.12 on the originating box. I was told that I shouldn't use the views directly. I believe libpq or something just tried to push out all 30G of data all at once from the foreign box to the originating box. After I used the remote_select functions. All is better (for now) Thanks -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Connecting to Teradata via Postgresql
Hi All, Anyone here has a teradata box ? Are you able to connect to it from withing postgresql? I would like to pull 1or 2 tables from the box (sync) and was wondering if there's anyway to do that w/o using dbi-link. I actually am trying dbi-link but it seem as though it doesn't support DBD::Teradata. Based on the error descriptions, and some googling, seems like Teradata.pm does not support the "column_info" DBI method of getting the column descriptions and such. is there any other methods available? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] convert epoch to date
Hi all;
I know how to convert a date to an epoch:
select extract ('epoch' from timestamp '2009-08-12')
How do I do the opposite, I want to convert epoch values to a date
Thanks in advance
--
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] Connecting to Teradata via Postgresql
On Sun, Aug 30, 2009 at 11:21:55PM +0800, Ow Mun Heng wrote: > Hi All, > > Anyone here has a teradata box? Are you able to connect to it from > withing postgresql? If you can get me a test environment including Teradata, I'd be delighted to add support to DBI-Link, most likely in the form of examples. Cheers, David. -- David Fetter http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] Lotus Approach like "find" function (ie CTRL-F) in some libre forms client?
On Saturday 29 August 2009 4:59:14 pm Zenaan Harkness wrote: > I am charged with migrating a Lotus Approach db + forms. > > Approach (97 I think :) has this function where, when viewing a record > or within a form (or in the spreadsheet view for that matter) one can > press CTRL-F, for "find records". > > The form I am on becomes a blank form (all fields cleared, but all > fields for that form still present on the form) and I can enter text > into any field, with wildcards. Primarily * to represent zero or more > characters is what we use. > > We are just learning PG, as our Approach replacement. > > Is there a libre db forms client which we can use to get this same > functionality? > > If not, as a last resort backup plan, can anyone recommend a proprietary > db forms client which can achieve this functionality? > > > Just to be clear on how Approach works: > > The "select" that occurs when pressing ENTER is an AND of each field's > select criteria. This allows us to do a more specific search of records, > by entering (if we know it) a small portion of extra data (eg part of a > phone number), or a more general search (enter less of the name field > for example, perhaps I just misspelt it and that's why I got no search > results, so I just shorten the name to the part that I know must be > spelt right, adding wildcards where appropriate. > > For example if searching for a Susan Macaby, and I don't know if she has > a middle name or not, and I'm not sure on the spelling of her last name, > I might search for "Susan*Ma*b*" (without the quotes) which will be > pretty well guaranted to bring up her record. But where I know more > information I put it, with less or no wildcards, to cause a faster > search to occur. > > > TIA > Zenaan I have built similar functionality using Dabo (http://dabodev.com/). Another option is to use the Base component of OpenOffice. If you have access to a copy of OO then enter the following search term (data;filtering in forms) in the Help|Index Search term dialog for a more detailed explanation. -- Adrian Klaver akla...@comcast.net -- 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 libpython2.3.so.1.0
On Saturday 29 August 2009 4:33:14 pm nesfree wrote: > Hello, > > I am not experienced PostgreSQL user but have following problem: > > I am unable to use plpythonu on linux (ubuntu 9.04). After I execute > command: > > psql# CREATE LANGUAGE plpythonu; > > I got following error message: > > ERROR: could not load library > "/opt/PostgreSQL/8.4/lib/postgresql/plpython.so": libpython2.3.so.1.0: > cannot open shared object file: No such file or directory > > I usually use python2.6.2... > > P.S. I can do that on windows,but I am not fan on it. > > Best regards, > Predrag I guess the first question is how did you install Postgres? -- Adrian Klaver akla...@comcast.net -- 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] convert epoch to date
Hello
2009/8/30 Kevin Kempter :
> Hi all;
>
> I know how to convert a date to an epoch:
>
> select extract ('epoch' from timestamp '2009-08-12')
>
postgres=# select extract ('epoch' from timestamp '2009-08-12');
date_part
1250028000
(1 row)
Time: 0,734 ms
postgres=# select to_timestamp(1250028000);
to_timestamp
2009-08-12 00:00:00+02
(1 row)
regards
Pavel Stehule
>
> How do I do the opposite, I want to convert epoch values to a date
>
>
> Thanks in advance
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Using WITH queries on VIEWs
Hi! Is it possible to use WITH queries ( http://www.postgresql.org/docs/8.4/interactive/queries-with.html) on VIEWs? I have a rather complex view that I could optimize with it... Regards, -- Jorge Godoy
[GENERAL] $Body$
Hi List; What does it mean the $BODY$ when writing the function? In other words: why to use the $ sign? Regards Bilal -- 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] $Body$
It's just a string constant: http://www.postgresql.org/docs/8.4/interactive/sql-syntax-lexical.html#SQL-SYNTAX-CONSTANTS It could be anything between the dollar signs, but BODY is self- documenting. On Aug 30, 2009, at 3:37 PM, bilal ghayyad wrote: Hi List; What does it mean the $BODY$ when writing the function? In other words: why to use the $ sign? Regards Bilal -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- -- Christophe Pettus x...@thebuild.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] $Body$
On Sun, Aug 30, 2009 at 03:37:56PM -0700, bilal ghayyad wrote: > Hi List; > > What does it mean the $BODY$ when writing the function? In other > words: why to use the $ sign? See http://www.postgresql.org/docs/current/static/sql-syntax-lexical.html#SQL-SYNTAX-DOLLAR-QUOTING Cheers, David. -- David Fetter http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] Using WITH queries on VIEWs
On Sun, Aug 30, 2009 at 07:33:52PM -0300, Jorge Godoy wrote: > Hi! > > > Is it possible to use WITH queries ( > http://www.postgresql.org/docs/8.4/interactive/queries-with.html) on VIEWs? > > I have a rather complex view that I could optimize with it... A WITH query can go anywhere any other read-only (for now) row-returning query can, so top-level SELECTs, sub-selects, etc. Cheers, David. -- David Fetter http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] short circuit joins
Hi, -- a select ... from base_table bt left outer join sub_table_1 st1 on (st1.base_table = bt.id) left outer join sub_table_2 st1 on (st2.base_table = bt.id) -- b select ... from base_table bt left outer join sub_table_1 st1 on (bt.objecttype = 1 AND st1.base_table = bt.id) left outer join sub_table_2 st1 on (bt.objecttype = 2 AND st2.base_table = bt.id) Pretend this is some stupid way of object orientation. base_table is the base class and sub_table_x are subclasses. base_table.objecttype tells which instance it is. Just for the sake of discussion, it could also be like "bt.is_married_monogamous = 1 AND wife.husband = bt.id" for example. In case b, does Postgres avoid to unnecessarily try look for respective sub_table ? Is it worthwhile to have? I'm on 8.3 presently. Still curious if newer versions have some optimization here. Best regards, Marcus -- 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] $Body$
This is the idea david: Why in the constant string we use the tag and we do not use the tag in the BODY? In other wrods, why we write it $BODY$ and does not write it as $q$BODY$q$ or as $$BODY$$? Also why in the single-quote syntax, each backslash will be written as four backslashes? While this will not be needed in the dolar-quoted string? Regards Bilal --- On Sun, 8/30/09, David Fetter wrote: > From: David Fetter > Subject: Re: [GENERAL] $Body$ > To: "bilal ghayyad" > Cc: pgsql-general@postgresql.org > Date: Sunday, August 30, 2009, 6:44 PM > On Sun, Aug 30, 2009 at 03:37:56PM > -0700, bilal ghayyad wrote: > > Hi List; > > > > What does it mean the $BODY$ when writing the > function? In other > > words: why to use the $ sign? > > See > http://www.postgresql.org/docs/current/static/sql-syntax-lexical.html#SQL-SYNTAX-DOLLAR-QUOTING > > Cheers, > David. > -- > David Fetter > http://fetter.org/ > Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: > dfetter > Skype: davidfetter XMPP: david.fet...@gmail.com > > Remember to vote! > Consider donating to Postgres: http://www.postgresql.org/about/donate > -- 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] $Body$
On Sun, Aug 30, 2009 at 04:13:18PM -0700, bilal ghayyad wrote: > This is the idea david: > > Why in the constant string we use the tag and we do not use the tag > in the BODY? > > In other wrods, why we write it $BODY$ and does not write it as > $q$BODY$q$ or as $$BODY$$? The string BODY in $BODY$ has no syntactical significance. You could just as easily use $$ or $supercalifragilisticexpialidocious$. The only place where the string inside matters is when you're nesting dollar quoted structures. > Also why in the single-quote syntax, each backslash will be written > as four backslashes? While this will not be needed in the > dolar-quoted string? Dollar quoting was specifically invented so that people would not have to use torrents of backslashes and/or single quotes. Cheers, David. -- David Fetter http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Query and the number of row result
Hi All; After doing a SELECT query, how can I know the number of returned rows from this query? If it returned 5 rows or 1 row or non? Regards Bilal -- 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] Query and the number of row result
On Sun, Aug 30, 2009 at 06:56:59PM -0700, bilal ghayyad wrote: > Hi All; > > After doing a SELECT query, how can I know the number of returned > rows from this query? If it returned 5 rows or 1 row or non? Lots of language bindings have this. Which language(s) are you using? Cheers, David. -- David Fetter http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] indexes on partitioned tables - on the base table, or the partitioned tables?
Hi all; where's the best place for the indexes/constraints on a partitioned table. I assume it's best to place the FK constraints/triggers on the base/master table and the indexes on the individual partition tables. Thoughts? Thanks in advance. -- 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] Query and the number of row result
Postgresql. Is there alot of Postgresql? How can I know mine? Regards Bilal --- On Sun, 8/30/09, David Fetter wrote: > From: David Fetter > Subject: Re: [GENERAL] Query and the number of row result > To: "bilal ghayyad" > Cc: pgsql-general@postgresql.org > Date: Sunday, August 30, 2009, 10:12 PM > On Sun, Aug 30, 2009 at 06:56:59PM > -0700, bilal ghayyad wrote: > > Hi All; > > > > After doing a SELECT query, how can I know the number > of returned > > rows from this query? If it returned 5 rows or 1 row > or non? > > Lots of language bindings have this. Which > language(s) are you using? > > Cheers, > David. > -- > David Fetter > http://fetter.org/ > Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: > dfetter > Skype: davidfetter XMPP: david.fet...@gmail.com > > Remember to vote! > Consider donating to Postgres: http://www.postgresql.org/about/donate > -- 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] Query and the number of row result
On Sun, Aug 30, 2009 at 07:35:42PM -0700, bilal ghayyad wrote: > Postgresql. > > Is there alot of Postgresql? How can I know mine? Are you connecting from C? PHP? Perl? Python? Ruby? Cheers, David. -- David Fetter http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
