Re: [GENERAL] Find difference between two Text fields

Hi, there might be a better solution out there, but it seemed like an interesting problem so I wrote this function: CREATE OR REPLACE FUNCTION stringdiff(text, text) RETURNS TEXT AS $$ SELECT array_to_string(ARRAY( SELECT CASE WHEN substring($1 FROM n FOR 1) = substring(

Re: [GENERAL] Inserts into sl_log tables timing out (related to the dataloss bug)

Accidentally sent this to pgsql-general instead of slony1-general. Sorry about that. :( -- Aleksander Aleksander Kmetec wrote: Hi, there's another bug I've been seeing from time to time, but was unable to reproduce it until today. -- Sent via pgsql-general mailing list (pgs

[GENERAL] Inserts into sl_log tables timing out (related to the dataloss bug)

Hi, there's another bug I've been seeing from time to time, but was unable to reproduce it until today. We have statement_timeout set to 10 seconds and we would sometimes get timeouts on extremely simple statements, like single-row inserts. I believe this is caused by a waiting TRUNCATE in log

Re: [GENERAL] Cast for text->Integer missing in 8.3.5

Nykolyn, Andrew wrote: I am trying to upgrade my Postgres server from 8.2.3 to 8.3.5 and have found that the cast text->integer and integer->text are missing. Is there a reason why they are not there and how can I get them back. I have many stored procedures that rely on those casts I'm ri

Re: [GENERAL] Trying to create implicit casts to text in PG 8.3

Emanuel Calvo Franco wrote: You add the cast's but not the operator. The cast is in other way (try to compare with a text '13') for example. Thanks. I added both "||(double precision, text)" and "||(text, double precision)" operators and it works now. But I'm wondering: do I need to do anyth

[GENERAL] Trying to create implicit casts to text in PG 8.3

Hi, everyone. I'm trying to upgrade a database which is used by several hundred installations of an app; with each installation possibly running some custom code and 3rd party extensions. I was hoping that it would be possible to re-add implicit casts to text for bacwards compatibility with 8.2

[GENERAL] Last insert/update/delete time for a table

Hi all, I'm in the process of writing a backup script which only dumps those tables which were changed in the last 24 hours. We have hundreds of tables (one per user), but most of them don't get changed very often, so there's no point in dumping them every night. Is there a way to get the tim

[GENERAL] Getting the search_path value for a query listed in pg_stat_activity output (feature request?)

Hi, all. Is there a way to find out which search_path a query is using? I have around 100 schemas, each of them containing the same set of tables. When the app connects it sets the search_path and then doesn't use the schema name anywhere again, which means that it's impossible to see which exa

[GENERAL] Partitioning on IS NULL / IS NOT NULL not supported?

Hi, I'm trying to split a table into 2 partitions based on whether a field's value is NULL or NOT NULL, but when I run "EXPLAIN SELECT * FROM tab WHERE version IS NULL" it shows that both partitions are being scanned, not just the one which contains rows where version is null. Is this not sup

[GENERAL] Custom index type for indexing long texts?

Hi, I'm looking for a solution for indexing long TEXT columns. We're currently using a HASH index, which can handle most situations, but every now and then we need support for even longer texts. One solution would be to create a functional index which would only use the first N chars of mycol,

Re: [GENERAL] Unpredicatable behavior of volatile functions used

Tom Lane wrote: Aleksander Kmetec <[EMAIL PROTECTED]> writes: Some quick testing shows that util.row_number() only gets re-evaluated at every call if the subquery contains an ORDER BY clause. Now we can predict whether we need to compensate for that just by looking at the original query

Re: [GENERAL] Unpredicatable behavior of volatile functions used

Tom Lane wrote: This isn't gonna work very well if your query involves sorting, because the SELECT-list is evaluated before the sort step ... regards, tom lane Thanks, this seems to solve my problem. Some quick testing shows that util.row_number() only gets re-evalua

[GENERAL] Unpredicatable behavior of volatile functions used in cursors

Hi, I'm running into some inconsistent behavior when using volatile functions with cursors under PG 8.1. We're using the following technique for counting the number of rows in a cursor: --- DECLARE instance_cur_1 SCROLL CURSOR FOR SELECT util.row_number(), * FROM ( $LONG_RUNNIN

Re: [GENERAL] index type for indexing long texts

Thank you both for your suggestions. I think I'll try the GiST approach first since using an existing contrib extension as a starting point seems like a simpler task for someone like me. :) Regards, Aleksander Richard Troy wrote: Aleksander Kmetec <[EMAIL PROTECTED]> writes:

[GENERAL] index type for indexing long texts

(I'm reposting this because the original message didn't make it through in the last ~20 hours) Hi, I'm looking for a solution for indexing long TEXT columns. We're currently using a HASH index, which can handle most situations, but every now and then we need support for even longer texts. On

[GENERAL] reindexdb and "could not open relation" error message

Hi! For the past two days I've been getting the following errors from my cron scripts which connect to the database: day 1, on a SELECT statement: ERROR: could not open relation 1663/35154/221872: No such file or directory day 2, on a LOCK TABLE statement: ERROR: could not open relation 1663