[GENERAL] Mapping Java BigDecimal
Hi All, We decide add support PostgreSQL database (now supporting only Oracle database) to our product. In Oracle we mapping Java BigDecimal to number(19, 2), in PostgreSQL to numeric(19, 2). If I store to BigDecimal column number without decimal, e.g. 3, than Oracle JDBC driver return 3, but PostgreSQL JDBC driver return 3.00. Is there some way (mapping, server setup,...) how reach return number without trailing zeroes on decimal position? I'm using PostgreSQL v. 8.1.18 (default for CentoOS 5.3). Thank you all Jakub Bednar -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] does ispell have allaffixes set to on?
I was testing the ispell text search dictionary and it appears to be behaving as
if the ispell option allaffixes was set to on. This wasn't the case for the
original tsearch2 contrib module, and for the ispell program itself which
defaults to off.
So for example, if I create a simple DictFile with an entry for the word brand
(brand/DGRS) and a simple english affix AffFile that does those standard
ispell suffixes (*D ED, *G ING, *R ER and *S S) along with the standard
ispell prefixes (*A: . RE, *I: . IN, *U . UN) then the ispell dictionary
will return a lexeme for any input token containing a suffix and one of those
prefixes even though NONE of the prefixes have been listed in the dictionary
file as active for that word.
The following is observed and expected:
mydb= CREATE TEXT SEARCH DICTIONARY test_ispell (
TEMPLATE = ispell,
DictFile = test,
AffFile = test,
StopWords = english );
mydb= SELECT
ts_lexize('test_ispell', 'branding') AS sfx_yes,
ts_lexize('test_ispell', 'brandest') AS sfx_no,
ts_lexize('test_ispell', 'notindict') AS dict_no,
ts_lexize('test_ispell', 'rebrand') AS pfx_no;
sfx_yes | sfx_no | dict_no | pfx_no
-++-+
{brand} || |
(1 row)
However, the following results are NOT expected:
mydb= SELECT
ts_lexize('test_ispell', 'unbranded') AS sfx_wpfx1,
ts_lexize('test_ispell', 'rebranding') AS sfx_wpfx2;
sfx_wpfx1 | sfx_wpfx2
---+---
{brand} | {brand}
(1 row)
In that second statement I expect NULL values indicating that the tokens are
unknown, rather than lexemes indicating a match. Is this expected behavior or a
bug, and is there any way to control this? Before I try to patch this in the
code I'd like to know if it's intentional behavior or not.
It gets even screwier if you add rebrand to the dictionary (e.g. rebrand/DGS).
Then ts_lexize('test_ispell', 'rebranding') returns an array of both lexemes
{rebrand,brand}, when only the first is anticipated and wanted.
Thanks,
Brian Carp
--
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] Incorrect FTS query results with GIN index
Basically, I started testing prefix matching in FTS and got into troubles. Self-contained example follows: Thank you, fixed. The reason was in incorrect optimization of GIN scan: GIN reuses scan result for equals key, but comparison of key didn't take into account a difference of scan's strategy. -- 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] Incorrect FTS query results with GIN index
Great, thank you! I assume this one goes into 8.4.3, right? 2010/1/18 Teodor Sigaev teo...@sigaev.ru Basically, I started testing prefix matching in FTS and got into troubles. Self-contained example follows: Thank you, fixed. The reason was in incorrect optimization of GIN scan: GIN reuses scan result for equals key, but comparison of key didn't take into account a difference of scan's strategy. -- Teodor Sigaev E-mail: teo...@sigaev.ru WWW: http://www.sigaev.ru/
Re: [GENERAL] Incorrect FTS query results with GIN index
Great, thank you! I assume this one goes into 8.4.3, right? Yeah, or apply patch http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/backend/access/gin/ginscan.c?r1=1.25r2=1.26 -- 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] postgres external table
Craig Ringer wrote: For those non-Oracle users among us, what's an external table? External tables let you map a text file directly to a table without explicitly loading it. In PostgreSQL, if you have data in a CSV file, usually you'd import it with COPY before you'd use it. If external tables were available, you'd just say there's an external table as a CSV file and you could start running queries against it. So the quick answer is no, there is no built-in external table support in PostgreSQL; normally people load the data using COPY instead. There's a work in progress on this topic at http://pgfoundry.org/projects/pgexternaltable/ , but it's extremely rough at this point and I'm not sure if it's even moving in the right direction--the main project would do something like this via SQL/MED, and I don't think that's how the prototype is being built at all. The only PostgreSQL-based product I'm aware of that has working external table support already is Greenplum DB. -- Greg Smith2ndQuadrant Baltimore, MD PostgreSQL Training, Services and Support g...@2ndquadrant.com www.2ndQuadrant.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] postgres external table
Greg Smith g...@2ndquadrant.com writes: Craig Ringer wrote: For those non-Oracle users among us, what's an external table? External tables let you map a text file directly to a table without explicitly loading it. In PostgreSQL, if you have data in a CSV file, usually you'd import it with COPY before you'd use it. If external tables were available, you'd just say there's an external table as a CSV file and you could start running queries against it. I'm finding it hard to visualize a use-case for that. We must postulate that the table is so big that you don't want to import it, and yet you don't feel a need to have any index on it. Which among other things implies that every query will seqscan the whole table. Where's the savings? regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] postgres external table
On Mon, Jan 18, 2010 at 09:57:02AM -0500, Tom Lane wrote:
Greg Smith g...@2ndquadrant.com writes:
Craig Ringer wrote:
For those non-Oracle users among us, what's an external table?
External tables let you map a text file directly to a table without
explicitly loading it. In PostgreSQL, if you have data in a CSV file,
usually you'd import it with COPY before you'd use it. If external
tables were available, you'd just say there's an external table as a CSV
file and you could start running queries against it.
I'm finding it hard to visualize a use-case for that. We must postulate
that the table is so big that you don't want to import it, and yet you
don't feel a need to have any index on it. Which among other things
implies that every query will seqscan the whole table. Where's the
savings?
I've mostly wanted something like it when I've been dealing with
externally maintained data. The best idea I've seen so far has just
been a function similar to:
copy_csv_from(filename text) returns setof text[]
to be used as:
SELECT d[0]::Int AS id, d[1] AS name
FROM copy_csv_from('/tmp/usernames.csv') d;
This could be wrapped in a VIEW giving what I'd expect to be similar
semantics to an external table, however I've never used one so I could
be missing something.
It's possible to write this function at the moment, it's somewhat
suboptimal as the csv file is completely imported before anything else
happens so is only good for small files.
--
Sam http://samason.me.uk/
--
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] postgres external table
On Mon, Jan 18, 2010 at 2:57 PM, Tom Lane t...@sss.pgh.pa.us wrote: I'm finding it hard to visualize a use-case for that. We must postulate that the table is so big that you don't want to import it, and yet you don't feel a need to have any index on it. Which among other things implies that every query will seqscan the whole table. Where's the savings? I think it's usually more my data is updated by other tools and it would be hard/impossible/annoying to insert another step into the pipeline to copy it to yet another place. The main benefit is that you can access the authoritative data directly without having to copy it and have some sort of process in place to do that regularly. Text files are kind of useless but they're a baseline bit of functionality on top of which to add more sophisticated external forms such as data available over at some url or over some kind of rpc -- to which various conditions could be pushed using external indexes -- or ultimately in another database to which whole joins can be pushed. -- greg -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Tamaño de campo
Hola Me gustaría saber si es posible devolver el tamaño de un campo de una determinada tabla. Es decir, si yo defino por ejemplo un campo de tipo character varying (8), sería posible devolver el número 8?? o en su defecto el número de bytes máximo de ese campo?? Saludos
Re: [GENERAL] postgres external table
On Mon, Jan 18, 2010 at 7:57 AM, Tom Lane t...@sss.pgh.pa.us wrote: Greg Smith g...@2ndquadrant.com writes: Craig Ringer wrote: For those non-Oracle users among us, what's an external table? External tables let you map a text file directly to a table without explicitly loading it. In PostgreSQL, if you have data in a CSV file, usually you'd import it with COPY before you'd use it. If external tables were available, you'd just say there's an external table as a CSV file and you could start running queries against it. I'm finding it hard to visualize a use-case for that. We must postulate that the table is so big that you don't want to import it, and yet you don't feel a need to have any index on it. Which among other things implies that every query will seqscan the whole table. Where's the savings? I've used it mostly for importing in the past. Saves the step of loading a large file into a table with no constraints as a middle step. -- 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] postgres external table
Tom Lane wrote: I'm finding it hard to visualize a use-case for that. We must postulate that the table is so big that you don't want to import it, and yet you don't feel a need to have any index on it. Which among other things implies that every query will seqscan the whole table. Where's the savings? I've mainly seen it used for data loading where there's some sort of transformation going on, typically to cleanup junk fields that would fail a constraint or derive new columns. If you have external tables, there's no need to load the data into a temporary table if all you're going to do is modify a few things and then write the result to somewhere else. Most of these use cases process the whole file anyway, so having to do a whole scan isn't an issue. I used to run an app that imported gigabytes a day of text files dumped from another server that used a weird date format I had to process via pl/pgsql function. Having to pass them through COPY and then INSERT processed versions to somewhere else was really a drag, given that there was no use for the intermediate data. It also can be handy for bootstrapping apps that are converting stuff out of a legacy system too. Just make the mainframe/whatever dump a new text file periodically into where the external table looks for its data, and you skip having to schedule reloads when the content changes. Can make your life easier while running the two systems in parallel initially. -- Greg Smith2ndQuadrant Baltimore, MD PostgreSQL Training, Services and Support g...@2ndquadrant.com www.2ndQuadrant.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] postgres external table
2010/1/18 Tom Lane t...@sss.pgh.pa.us: Greg Smith g...@2ndquadrant.com writes: Craig Ringer wrote: For those non-Oracle users among us, what's an external table? External tables let you map a text file directly to a table without explicitly loading it. In PostgreSQL, if you have data in a CSV file, usually you'd import it with COPY before you'd use it. If external tables were available, you'd just say there's an external table as a CSV file and you could start running queries against it. I'm finding it hard to visualize a use-case for that. We must postulate that the table is so big that you don't want to import it, and yet you don't feel a need to have any index on it. Which among other things implies that every query will seqscan the whole table. Where's the savings? regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general Another case, Tom, could be when the file is updated from a non-DB application and you need to synchronize the data with other DB applications ... -- Vincenzo Romano NotOrAnd Information Technologies NON QVIETIS MARIBVS NAVTA PERITVS -- 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] Collate order on Mac OS X, text with diacritics in UTF-8
Very interesting discussion indeed. It seems that Postgresql:The world's most advanced open source database can not work properly on Mac OS X: the world's most advanced operating system and FreeBSD. Don't you think postgresql.org should remove from their download page the links to FreeBSD and Mac OS X binary packages? Martin Flahault Le 18 janv. 2010 à 08:10, Martijn van Oosterhout a écrit : On Sat, Jan 16, 2010 at 09:10:53PM +, Greg Stark wrote: Switching to ICU means trading our current inconsistency from platform to platform for a different inconsistency which would be better in some cases and worse in others. Or, you can have the cake and eat it too. That is, aim for the end goal and let people choose what library they want to use for sorting (that is, extend the meaning of the locale identifier). Patches for this should be in the archives somewhere. As I recall the reason this was rejected is that *BSD lack the capability of handling multiple collation algorithms at all at the libc level (that is, if you don't just tell people to use ICU in that case). Mac OS X doesn't have great POSIX locale support but at least they implemented strcoll_l. Have a nice day, -- Martijn van Oosterhout klep...@svana.org http://svana.org/kleptog/ Please line up in a tree and maintain the heap invariant while boarding. Thank you for flying nlogn airlines. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] number of page slots needed (1576544) exceeds max_fsm_pages (204800)]
Does this max_fsm_pages value seem OK for a 46GB database?
I've clustered all the tables that seemed to be exhibiting large amounts
of bloat.
reporting=# SELECT pg_size_pretty(pg_database_size('reporting'));
pg_size_pretty
46 GB
(1 row)
NOTICE: number of page slots needed (1576544) exceeds max_fsm_pages (204800)
HINT: Consider increasing the configuration parameter max_fsm_pages to a
value over 1576544.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] PostreSQL PostgreSQL 8.1.18 and Tsearch2 context error
Hi guys,
I'm working with full-text search on my project, however i'm receiving an
Config file error: 7 ERROR: no tsearch config CONTEXT message but i don't
know what it means.
The entire error message received is:
SQLSTATE[F]: Config file error: 7 ERROR: no tsearch config CONTEXT: SQL
statement SELECT doc_id, area_tem_id, headline(doc_nombre,
to_tsquery('spanish', 'prueba')) AS doc_nombre, headline(doc_autor,
to_tsquery('spanish', 'prueba')) AS doc_autor, doc_agno_publicacion,
headline(doc_descripcion, to_tsquery('spanish', 'prueba')) AS
doc_descripcion, doc_visitas, rank(doc_tsv, query) AS rank FROM
tbl_documento, to_tsquery('spanish', 'prueba') query WHERE query @@ doc_tsv
AND doc_activo='1' ORDER BY rank DESC, area_tem_id ASC PL/pgSQL function
buscardocs line 26 at for over execute statement
Can you help me about this error?, i will apreciate so much any suggestion
about it.
Thank's in advance,
Rodrigo
[GENERAL] Updating from a column
Hi I have a table that has one column (pump1) controlled by a dbcheckbox. The values can be True, False or null. I want to insert a row of values into second table when column pump1 value is 'True'. I don't want the trigger to insert a row when other columns of the first table are updated or when the pump1 column value becomes 'False'. I would appreciate any suggestions as to how to accomplish this. Thanks in advance. Bob
Re: [GENERAL] number of page slots needed (1576544) exceeds max_fsm_pages (204800)]
On Mon, Jan 18, 2010 at 1:47 PM, Reid Thompson reid.thomp...@ateb.com wrote: Does this max_fsm_pages value seem OK for a 46GB database? I've clustered all the tables that seemed to be exhibiting large amounts of bloat. My big DB is about 70 on disk. I have fsm pages set to 3.4 million, and occasionally that gets overrun. It is nearly catastrophic to us when that happens as performance takes a serious nose dive. This is probably the major reason switching to 8.4 is high on our list. Our DB has a *lot* of data churn, and that makes a lot of pages with space on them to track. One more thing you may wish to consider is running re-index on your tables. I found that a lot of pages with empty space were compacted and the number of fsm entries went down significantly when I did this last week. For me this was more important than running cluster to pack the data tables themselves. -- 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] number of page slots needed (1576544) exceeds max_fsm_pages (204800)]
On Mon, Jan 18, 2010 at 02:43:11PM -0500, Vick Khera wrote: On Mon, Jan 18, 2010 at 1:47 PM, Reid Thompson reid.thomp...@ateb.com wrote: Does this max_fsm_pages value seem OK for a 46GB database? I've clustered all the tables that seemed to be exhibiting large amounts of bloat. One more thing you may wish to consider is running re-index on your tables. I found that a lot of pages with empty space were compacted and the number of fsm entries went down significantly when I did this last week. For me this was more important than running cluster to pack the data tables themselves. It was my belief that cluster would re-build the indexes as part of the cluster operation. Is that belief incorrect? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Equivalent of mysql type mediablob in postgres?
I would like to move a table that is used to store images from mysql to postgres. The only stumbling I may encounter, may be switching from mysql blob to something in postgres. We store chart images in a mysql medium blob type. How can I store these in postgres? Chris | IMAGE | mediumblob | NO | | | | _
Re: [GENERAL] Equivalent of mysql type mediablob in postgres?
Chris Barnes wrote on 18.01.2010 21:05: I would like to move a table that is used to store images from mysql to postgres. The only stumbling I may encounter, may be switching from mysql blob to something in postgres. We store chart images in a mysql medium blob type. How can I store these in postgres? bytea is the datatype you are looking for http://www.postgresql.org/docs/current/static/datatype-binary.html Thomas -- 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] Updating from a column
On Monday 18 January 2010 11:31:57 am Bob Pawley wrote: Hi I have a table that has one column (pump1) controlled by a dbcheckbox. The values can be True, False or null. I want to insert a row of values into second table when column pump1 value is 'True'. I don't want the trigger to insert a row when other columns of the first table are updated or when the pump1 column value becomes 'False'. I would appreciate any suggestions as to how to accomplish this. Thanks in advance. Bob Create an INSERT, UPDATE trigger on table1. Have the trigger inspect the value of pump1. You will need to guard against double entry on updates. So rough flow is: if TG_OP = 'INSERT' and NEW.pump1 = 't' INSERT row second table if TG_OP = 'UPDATE' and NEW.pump1='t' if OLD.pump1 = 'f' or OLD.pump1 is NULL INSERT row second table -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] type of field
Hi list, I've got to store many small videos; to make things simple (backup/restore, because users don't know very much about IT) I've choosen BYTEA + EXTERNAL, is it the right choice? JY -- Wait for that wisest of all counselors, Time. -- Pericles -- 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] type of field
On Mon, Jan 18, 2010 at 8:24 PM, Jean-Yves F. Barbier 12u...@gmail.com wrote: I've got to store many small videos; to make things simple (backup/restore, because users don't know very much about IT) I've choosen BYTEA + EXTERNAL, is it the right choice? If you want to store them in the database then yes, that sounds right to me. The debate over whether to store things like this in the database or outside won't be resolved on this thread but you should be aware of the pros and cons of each method. It sounds like you've done some research already. -- greg -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] data dump help
Hello, Sorry for the poor subject. Not sure how to describe what I need here. I have an application that logs to a single table in pgsql. In order for me to get into our log management, I need to dump it out to a file on a periodic basis to get new logs. I am not sure how to tackle this. I thought about doing a date calculation and just grabbing the previous 6 hours of logs and writing that to a new log file and setting up a rotation like that. Unfortunately, the log management solution can't go into pgsql directly. Thoughts? Thanks! -- 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] type of field
Greg Stark a écrit : On Mon, Jan 18, 2010 at 8:24 PM, Jean-Yves F. Barbier 12u...@gmail.com wrote: I've got to store many small videos; to make things simple (backup/restore, because users don't know very much about IT) I've choosen BYTEA + EXTERNAL, is it the right choice? If you want to store them in the database then yes, that sounds right to me. The debate over whether to store things like this in the database or outside won't be resolved on this thread but you should be aware of the pros and cons of each method. It sounds like you've done some research already. Yeah, you're right: dunno wanna feed the Troll :) I choosed this solution because people are only users, they can't afford a full time admin they can't loose many time in complicated procedures, so the backup/restore procedure must stay as simple as possible on big tapes. BTW, for my own knowledge, could you point me to pages that describes out-of-db big blobs (5GB) link/unlink store/wipe to/from DB? Thanks. -- Benson, you are so free of the ravages of intelligence -- Time Bandits -- 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] data dump help
On 1/18/2010 4:08 PM, Terry wrote: Hello, Sorry for the poor subject. Not sure how to describe what I need here. I have an application that logs to a single table in pgsql. In order for me to get into our log management, I need to dump it out to a file on a periodic basis to get new logs. I am not sure how to tackle this. I thought about doing a date calculation and just grabbing the previous 6 hours of logs and writing that to a new log file and setting up a rotation like that. Unfortunately, the log management solution can't go into pgsql directly. Thoughts? Thanks! How about a flag in the db, like: dumped. inside one transactions you'd be safe doing: begin SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; select * from log where dumped = 0; -- app code to format/write/etc update log set dumped = 1 where dumped = 0; commit; Even if other transactions insert new records, you're existing transaction wont see them, and the update wont touch them. -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] data dump help
On Mon, Jan 18, 2010 at 4:48 PM, Andy Colson a...@squeakycode.net wrote: On 1/18/2010 4:08 PM, Terry wrote: Hello, Sorry for the poor subject. Not sure how to describe what I need here. I have an application that logs to a single table in pgsql. In order for me to get into our log management, I need to dump it out to a file on a periodic basis to get new logs. I am not sure how to tackle this. I thought about doing a date calculation and just grabbing the previous 6 hours of logs and writing that to a new log file and setting up a rotation like that. Unfortunately, the log management solution can't go into pgsql directly. Thoughts? Thanks! How about a flag in the db, like: dumped. inside one transactions you'd be safe doing: begin SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; select * from log where dumped = 0; -- app code to format/write/etc update log set dumped = 1 where dumped = 0; commit; Even if other transactions insert new records, you're existing transaction wont see them, and the update wont touch them. -Andy I like your thinking but I shouldn't add a new column to this database. It's a 3rd party application. -- 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] data dump help
On Mon, Jan 18, 2010 at 5:07 PM, Terry td3...@gmail.com wrote: On Mon, Jan 18, 2010 at 4:48 PM, Andy Colson a...@squeakycode.net wrote: On 1/18/2010 4:08 PM, Terry wrote: Hello, Sorry for the poor subject. Not sure how to describe what I need here. I have an application that logs to a single table in pgsql. In order for me to get into our log management, I need to dump it out to a file on a periodic basis to get new logs. I am not sure how to tackle this. I thought about doing a date calculation and just grabbing the previous 6 hours of logs and writing that to a new log file and setting up a rotation like that. Unfortunately, the log management solution can't go into pgsql directly. Thoughts? Thanks! How about a flag in the db, like: dumped. inside one transactions you'd be safe doing: begin SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; select * from log where dumped = 0; -- app code to format/write/etc update log set dumped = 1 where dumped = 0; commit; Even if other transactions insert new records, you're existing transaction wont see them, and the update wont touch them. -Andy I like your thinking but I shouldn't add a new column to this database. It's a 3rd party application. Although. I really like your idea so I might create another table where I will log whether the data has been dumped or not. I just need to come up with a query to check this with the other table. -- 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] number of page slots needed (1576544) exceeds max_fsm_pages (204800)]
On Mon, Jan 18, 2010 at 12:43 PM, Vick Khera vi...@khera.org wrote: On Mon, Jan 18, 2010 at 1:47 PM, Reid Thompson reid.thomp...@ateb.com wrote: Does this max_fsm_pages value seem OK for a 46GB database? I've clustered all the tables that seemed to be exhibiting large amounts of bloat. My big DB is about 70 on disk. I have fsm pages set to 3.4 million, and occasionally that gets overrun. It is nearly catastrophic to us We have about 2.8Million used and have it set to 10Million for the same reason as you do. -- 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] vacuum issues under load?
On Mon, Jan 18, 2010 at 12:28 AM, Martijn van Oosterhout klep...@svana.org wrote: On Fri, Jan 15, 2010 at 08:41:38AM -0700, Scott Marlowe wrote: With slony 2.0.3 or so, I had occasional complete lockups of my database that I didn't have time to troubleshoot as it was a live cluster and I had to restart slony and the databases to get them back up and running. With slony 2.0.2 I had similar problems. A CREATE INDEX on the slave caused slony on the slave to block while inserting into the table which eventually blocked the server during the log switch (TRUNCATE) which eventually blocked everything else. You could have used create index interactively. It occurs to me that the slony daemon should be able to get the TRUNCATE command to abort if it takes too long. No, I don't want it stopping my truncates. The problem I ran into was on a db with no creating indexes, no truncates nothing like that going on. It runs fine for a week or two, then hangs hard. No updates, no selects, nothing works. There are no locks in the way that I can see, just hung database. -- 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] vacuum issues under load?
On Mon, Jan 18, 2010 at 6:35 PM, Scott Marlowe scott.marl...@gmail.com wrote: You could have used create index interactively. s/interactively/concurrently/ -- 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] vacuum issues under load?
On Mon, Jan 18, 2010 at 6:35 PM, Scott Marlowe scott.marl...@gmail.com wrote: On Mon, Jan 18, 2010 at 12:28 AM, Martijn van Oosterhout klep...@svana.org wrote: On Fri, Jan 15, 2010 at 08:41:38AM -0700, Scott Marlowe wrote: With slony 2.0.3 or so, I had occasional complete lockups of my database that I didn't have time to troubleshoot as it was a live cluster and I had to restart slony and the databases to get them back up and running. With slony 2.0.2 I had similar problems. A CREATE INDEX on the slave caused slony on the slave to block while inserting into the table which eventually blocked the server during the log switch (TRUNCATE) which eventually blocked everything else. You could have used create index interactively. It occurs to me that the slony daemon should be able to get the TRUNCATE command to abort if it takes too long. No, I don't want it stopping my truncates. The problem I ran into was on a db with no creating indexes, no truncates nothing like that going on. It runs fine for a week or two, then hangs hard. No updates, no selects, nothing works. There are no locks in the way that I can see, just hung database. Wait a minute, I'm describing the scenario we had with 2.0.3, where it would just hang. The vacuum issue is different. Vacuum starts, slony goes to lock the table for ddl, waits on lock from vacuum, then everything backs up behind slony. With large numbers of tables, the problem gets much worse much faster. -- 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] postgres external table
On 19/01/2010 1:13 AM, Vincenzo Romano wrote: Another case, Tom, could be when the file is updated from a non-DB application and you need to synchronize the data with other DB applications ... How can that work without a transactional file system, though? If the external process writes to the file while you're half-way through reading it, what's the database to do? In general, how do external tables cope with the fact that they're on non-transactional storage? -- Craig Ringer -- 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] data dump help
On Mon, Jan 18, 2010 at 05:49:32PM -0600, Terry wrote: On Mon, Jan 18, 2010 at 5:07 PM, Terry td3...@gmail.com wrote: On Mon, Jan 18, 2010 at 4:48 PM, Andy Colson a...@squeakycode.net wrote: On 1/18/2010 4:08 PM, Terry wrote: Hello, Sorry for the poor subject. ?Not sure how to describe what I need here. ?I have an application that logs to a single table in pgsql. In order for me to get into our log management, I need to dump it out to a file on a periodic basis to get new logs. ?I am not sure how to tackle this. ?I thought about doing a date calculation and just grabbing the previous 6 hours of logs and writing that to a new log file and setting up a rotation like that. ?Unfortunately, the log management solution can't go into pgsql directly. ?Thoughts? Thanks! How about a flag in the db, like: dumped. inside one transactions you'd be safe doing: begin SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; select * from log where dumped = 0; -- app code to format/write/etc update log set dumped = 1 where dumped = 0; commit; Even if other transactions insert new records, you're existing transaction wont see them, and the update wont touch them. -Andy I like your thinking but I shouldn't add a new column to this database. ?It's a 3rd party application. Although. I really like your idea so I might create another table where I will log whether the data has been dumped or not. I just need to come up with a query to check this with the other table. Isn't this just over-engineering? Why not let the database do the work, and add the column with a default value of 0, so that you don't have to modify whatever 3rd-party app dumps the data: ALTER TABLE log ADD COLUMN dumped boolean DEFAULT FALSE (I don't do much ALTER TABLE, so that syntax may be all foobar'ed) - Bret -- 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] data dump help
2010/1/19 Bret S. Lambert bret.lamb...@gmail.com: Isn't this just over-engineering? Why not let the database do the work, and add the column with a default value of 0, so that you don't have to modify whatever 3rd-party app dumps the data: But what if his third-party software does something silly like a select * on the table and then gets a hissy fit because the data doesn't match the expectations any longer? Cheers, Andrej -- Please don't top post, and don't use HTML e-Mail :} Make your quotes concise. http://www.american.edu/econ/notes/htmlmail.htm -- 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] data dump help
On Tue, Jan 19, 2010 at 06:35:10PM +1300, Andrej wrote: 2010/1/19 Bret S. Lambert bret.lamb...@gmail.com: Isn't this just over-engineering? Why not let the database do the work, and add the column with a default value of 0, so that you don't have to modify whatever 3rd-party app dumps the data: But what if his third-party software does something silly like a select * on the table and then gets a hissy fit because the data doesn't match the expectations any longer? He said his app logs there, so I kind of assumed that it's write-only as far as the app is concerned. If not, then, yes, there could be issues. But why not keep things as simple as possible? -- 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] data dump help
Terry wrote: Hello, Sorry for the poor subject. Not sure how to describe what I need here. I have an application that logs to a single table in pgsql. In order for me to get into our log management, I need to dump it out to a file on a periodic basis to get new logs. I am not sure how to tackle this. I thought about doing a date calculation and just grabbing the previous 6 hours of logs and writing that to a new log file and setting up a rotation like that. Unfortunately, the log management solution can't go into pgsql directly. Thoughts? You do not indicate in your post, exactly how the data is stored, but I would assume there is a timestamp inside this single table. From my perspective there are 3 options available: Firstly, create a table that you can monitor when you have made dumps, typically with a column that will store a datetimestamp with now() in it. 1. You have access to the DB and you can schedule a pgAgent job to run every 6 hours that dumps the table into some usable format e.g. csv: SELECT Max(dumptimestamp) FROM dump_log INTO lastdump; currtime := now(); COPY (SELECT * FROM singletable WHERE timestamp lastdump) TO 'someexternaltable' DELIMETER ',' CSV HEADER ...; INSERT INTO dumplog (dumptimestamp) VALUES (currtime); 2. Same as above but run this as a trigger on your dumplog table when you need a dump by inserting the current_datetime into the dumplog table that will trigger a process to export the data. 3. You have an application that have an option to insert the current datetimestamp into your dumplog table and then read the exported table after completion. HTH, Johan Nel Pretoria, South Africa. -- 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] postgres external table
Having 'external tables' lets us avoid the step of loading data from a file into the table. We do not have to check whether a load job has run successfully, whether the data in the table really corresponds to the data in the file etc. It also lets us decide how many rejects can be allowed and so forth. http://www.adp-gmbh.ch/ora/misc/ext_table.html Other than that, I have not found any advantage. Jayadevan From: Tom Lane t...@sss.pgh.pa.us To: Greg Smith g...@2ndquadrant.com Cc: Craig Ringer cr...@postnewspapers.com.au, Amy Smith vah...@gmail.com, pgsql-general@postgresql.org Date: 01/18/2010 08:25 PM Subject:Re: [GENERAL] postgres external table Sent by:pgsql-general-ow...@postgresql.org Greg Smith g...@2ndquadrant.com writes: Craig Ringer wrote: For those non-Oracle users among us, what's an external table? External tables let you map a text file directly to a table without explicitly loading it. In PostgreSQL, if you have data in a CSV file, usually you'd import it with COPY before you'd use it. If external tables were available, you'd just say there's an external table as a CSV file and you could start running queries against it. I'm finding it hard to visualize a use-case for that. We must postulate that the table is so big that you don't want to import it, and yet you don't feel a need to have any index on it. Which among other things implies that every query will seqscan the whole table. Where's the savings? regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general DISCLAIMER: The information in this e-mail and any attachment is intended only for the person to whom it is addressed and may contain confidential and/or privileged material. If you have received this e-mail in error, kindly contact the sender and destroy all copies of the original communication. IBS makes no warranty, express or implied, nor guarantees the accuracy, adequacy or completeness of the information contained in this email or any attachment and is not liable for any errors, defects, omissions, viruses or for resultant loss or damage, if any, direct or indirect.
[GENERAL] SETOF Record Problem
By referring to http://www.postgresql.org/docs/current/static/xfunc-sql.html#XFUNC-SQL-TABLE-FUNCTIONS, section 34.4.8. SQL Functions Returning TABLE I create the following stored procedures. -- Function: get_measurements(bigint) -- DROP FUNCTION get_measurements(bigint); CREATE OR REPLACE FUNCTION get_measurements(IN bigint) RETURNS TABLE(_type text, _value double precision, _unit text) AS $BODY$DECLARE _lotID ALIAS FOR $1; BEGIN SELECT measurement_type.value, measurement.value, measurement_unit.value FROM unit, lot, measurement, measurement_unit, measurement_type WHERE lot_id = fk_lot_id AND fk_unit_id = unit_id AND fk_measurement_unit_id = measurement_unit_id AND fk_measurement_type_id = measurement_type_id AND lot_id = _lotID; END;$BODY$ LANGUAGE 'plpgsql' VOLATILE COST 100 ROWS 1000; ALTER FUNCTION get_measurements(bigint) OWNER TO postgres; However, whenever I call this function, using SELECT * FROM get_measurements(1); I get the following error : ERROR: query has no destination for result data HINT: If you want to discard the results of a SELECT, use PERFORM instead. CONTEXT: PL/pgSQL function get_measurements line 4 at SQL statement ** Error ** ERROR: query has no destination for result data SQL state: 42601 Hint: If you want to discard the results of a SELECT, use PERFORM instead. Context: PL/pgSQL function get_measurements line 4 at SQL statement But the example doesn't use any INTO or RETURN. Any hint? 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] Index on immutable function call
Hi, I'm trying to figure out how to use an index on an immutable function call in order to speed up queries. I came up with this small test: --create database foo; --drop table indexed_table; create table indexed_table ( id serial primary key, data1 integer, data2 integer ); create or replace function this_is_a_long_transformation(d integer) returns integer as $$ declare l integer; begin -- wait l = 0; while l 100 loop l = l + 1; end loop; -- return same value return d; end $$ language plpgsql immutable; -- insert data into table insert into indexed_table select i, cast((select random() * 1000 * i) as integer), cast((select random() * 1000 * i) as integer) from generate_series(1, 10) as i; -- create index create index long_transformation_index on indexed_table (this_is_a_long_transformation(data2)); --select * from indexed_table WHERE data1 data2; select * from indexed_table WHERE data1 this_is_a_long_transformation(data2); My goal is to make query... select * from indexed_table WHERE data1 this_is_a_long_transformation(data2); ... as fast as select * from indexed_table WHERE data1 data2; ... with the help of the index long_transformation_index. Unfortunately, Postgreql does not use the index at all. What am I doing wrong? I use the default query tuning options of Postgresql 8.3.7. Best regards, --- Philippe Lang Web: www.attiksystem.ch Attik SystemEmail : philippe.l...@attiksystem.ch rte de la Fonderie 2Phone : +41 26 422 13 75 1700 Fribourg Mobile : +41 79 351 49 94 Switzerland Fax: +41 26 422 13 76 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] C: extending text search: from where to start
I'd like to extend full text search so that I can transform tvectors in tquery and have direct access to a tvector as a record/array. I'm on Debian. This is my first experience with pg source code. I'd appreciate any pointer that will quickly put me on the right track. thanks -- 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] SETOF Record Problem
In response to Yan Cheng Cheok : By referring to http://www.postgresql.org/docs/current/static/xfunc-sql.html#XFUNC-SQL-TABLE-FUNCTIONS, section 34.4.8. SQL Functions Returning TABLE That's for language SQL, you are using: CREATE OR REPLACE FUNCTION get_measurements(IN bigint) LANGUAGE 'plpgsql' VOLATILE language plpgsql. That's not the same ... But the example doesn't use any INTO or RETURN. Sure? Read again: http://www.postgresql.org/docs/8.4/static/plpgsql-control-structures.html Rewrite your to RETURN insert your select here; Regards, Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: - Header) GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99 -- 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] Index on immutable function call
In response to Philippe Lang : Hi, I'm trying to figure out how to use an index on an immutable function call in order to speed up queries. My goal is to make query... select * from indexed_table WHERE data1 this_is_a_long_transformation(data2); ... as fast as select * from indexed_table WHERE data1 data2; ... with the help of the index long_transformation_index. Unfortunately, Postgreql does not use the index at all. What am I doing wrong? I use the default query tuning options of Postgresql 8.3.7. The planner doesn't know the actual parameter for the function, so he picked out the wrong plan. You can force the planner to re-planning with a dynamical statement within the function and EXECUTE that. http://www.postgresql.org/docs/current/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN In short: build a string that contains your query and EXECUTE that string, within your function. Regards, Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: - Header) GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
