Re: [SQL] organizing cron jobs in one function

PgAgent instead? -- Craig Ringer http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] organizing cron jobs in one function

On 11/19/2012 01:11 AM, Louis-David Mitterrand wrote: On Sun, Nov 18, 2012 at 07:27:54PM +0800, Craig Ringer wrote: On 11/18/2012 12:19 AM, Louis-David Mitterrand wrote: Hi, I'm planning to centralize all db maintenance jobs from a single pl/pgsql function called by cron every 15 minutes

Re: [SQL] find sudstring on text (data type) field failure

On 11/07/2012 04:36 PM, pantelis vlachos wrote: I was trying to find a substring on a text (data type) column like 'cat foo dog ...'. I use the query below SELECT id FROM table WHERE name LIKE '% foo %'; Sometimes the query return with nTuples=0 but there are matching rows. On retry, the

Re: [SQL] Calling the CTE for multiple inputs

. Without a schema to test with and some understanding of what the query does it's hard to say exactly. Wrapping it in a function is likely to be less efficient, but probably easier. -- Craig Ringer WITH cal AS ( SELECT generate_series('2011-02-02 00:00:00'::timestamp

Re: [SQL] ORDER BY COLUMN_A, (COLUMN_B or COLUMN_C), COLUMN_D

: regress=# SELECT min(x.a) FROM ( VALUES ('blah'),('blah2'),('') ) x(a); min -- (1 row) -- Craig Ringer -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] Tablesample Function on Postgres 9.1

://wiki.postgresql.org/wiki/TABLESAMPLE_Implementation) Sorry, that wiki page is just blue-sky speculation. If the feature were supported, you would find it in the main documentation. Wiki page updated to make that clearer. -- Craig Ringer -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org

Re: [SQL] Selecting data from XML

. Consider using PL/Python, PL/perl, PL/Java, or something like that to do the processing and return the resultset. -- Craig Ringer

Re: [SQL] Simple Upgrade from PostgreSQL version 8.1.11 (With schemas)

. -- Craig Ringer

Re: [SQL] How to limit access only to certain records?

http://www.postgresql.org/docs/current/static/sql-grant.html http://www.postgresql.org/docs/9.1/static/sql-grant.html http://www.postgresql.org/docs/current/static/sql-revoke.html http://www.postgresql.org/docs/9.1/static/sql-revoke.html Hope this helps. -- Craig Ringer

Re: [SQL] Is there a similarity-function that minds national charsets?

'), target_column ); Note that the flatten_accent function must be IMMUTABLE and can't access or refer to data in other tables, columns, etc nor SET (GUC) variables that might change at runtime. -- Craig Ringer -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your

Re: [SQL] Insane behaviour in 8.3.3

upgrades. ... and a lot more difficult :S Log monitoring is often the most imporant part - monitoring for NMIs and other hardware notifications, checking the kernel log for odd issues or reports of unexpected segfaults from userspace programs, etc. -- Craig Ringer -- Sent via pgsql-sql

Re: [SQL] Partitioned Tables

that. As a workaround, ignore the insert count for partitioned tables, or insert directly into the appropriate partition(s). -- Craig Ringer -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] how to use schema with data type

messges did you get? The first form looks reasonable to me, though I haven't tested. If you need to quote the schema for caps reasons, you'd use: xchromasun.weekly_mpr -- Craig Ringer -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http

Re: [SQL] JDBC Statement.setQueryTimeout : is there plan to implement this?

required to make it possible to implement setQueryTimeout, and nobody's come up with an acceptable patch. I haven't followed the issue so I could easily be mistaken, though. -- Craig Ringer -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http

Re: [SQL] JDBC Statement.setQueryTimeout : is there plan to implement this?

that using EJB timers is NOT the right way to do it - they're not supported by web profile containers and are really intended for business level timers that should be persistent across redeploy/relaunch of appserver/reboot. I've CC'd David Fetter, the author of the JDBC patch. -- Craig Ringer

Re: [SQL] JDBC Statement.setQueryTimeout : is there plan to implement this?

On 12/14/2011 11:30 PM, Sylvain Mougenot wrote: Thank you Craig Ringer for the detailed list of post (I found some by myself). Specially, I'm glad to see it is #1 TODO on the compliance matters. http://jdbc.postgresql.org/todo.html#Compliance As a reminder, I found post (on the net, not only

Re: [SQL] JDBC Statement.setQueryTimeout : is there plan to implement this?

Replied on pgsql-jdbc; please follow the discussion there. -- Craig Ringer -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] partitions versus databases

/in-postgresql-are-partitions-or-multiple-databases-more-efficient/ That'll help avoid duplication of effort, and make it easier for people searching for similar topics later to find out more. -- Craig Ringer

Re: [SQL] Query Timeout Question

driver uses that or not. Examine the ODBC `mylog' output after enabling psqlODBC debugging, and examine the server log after turning on query logging, so you can see what the ODBC driver actually asks the server for when you set a query timeout. -- Craig Ringer

Re: [SQL] No response from the backend

. -- Craig Ringer

Re: [SQL] No response from the backend

host, it's unlikely to be network connectivity. Check the server logs. -- Craig Ringer

Re: [SQL] pg_dump : problem with grant on table columns

On Nov 15, 2011 9:46 PM, Brice André br...@famille-andre.be wrote: Hello, I sometimes define some access rights on table columns instead of whole table. Everything works fine except when I perform a dump. When I dump a databse that contains such access rights, the pg_dump utility generates

Re: [SQL] How to implement Aggregate Awareness?

to recognise queries against a base table and rewrite it to use a materialized view of the table when it sees that the query only touches data collected by the materialized view. Right now, there isn't really anything for query rewriting like this to /target/ . -- Craig Ringer -- Sent via pgsql

Re: [SQL] How to write sql to access another odbc source.

easily using ODBC FDW (SQL/MED). -- Craig Ringer -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] Handling mutliple clients access with views

, and maybe should I handle all my clients with a single postgresql user, handling all safety aspect in my php script ? Nope, I heartily approve of doing security in-database, especially if you can do it declaratively. -- Craig Ringer -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org

Re: [SQL] Handling mutliple clients access with views

could switch to that without the app caring. -- Craig Ringer -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] how to temporally disable foreign key constraint check

that enforce the checks on particular tables. This must be done table-by-table, there's no global way to do it. Use ALTER TABLE ... DISABLE TRIGGER to do it. See: http://www.postgresql.org/docs/current/static/sql-altertable.html -- Craig Ringer -- Sent via pgsql-sql mailing list (pgsql-sql

Re: [SQL] Looking for a show create table name equivalent

. -- Craig Ringer POST Newspapers 276 Onslow Rd, Shenton Park Ph: 08 9381 3088 Fax: 08 9388 2258 ABN: 50 008 917 717 http://www.postnewspapers.com.au/ -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] best performance for simple dml

'), (11,'anne','smith'); There'll be a threshhold above which the COPY protocol becomes faster, though. -- Craig Ringer -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] Which version of PostgreSQL should I use.

in PostgreSQL 8.4 and above? If so, the issue isn't that pg_dump somehow fails to dump the bytea data. Rather, it's that drupal doesn't deal well with bytea data from newer versions of PostgreSQL until the bytea_output setting is changed to 'escape' because it doesn't understand the new hex format. -- Craig

Re: [SQL] Which version of PostgreSQL should I use.

feature rather than pg_dump and the app wasn't ready for the new bytea format. -- Craig Ringer -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] Problems Pgdump

/2004-04/msg00818.php -- Craig Ringer -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] Which version of PostgreSQL should I use.

On 23/05/2011 11:19 PM, jasmin.dizdare...@gmail.com wrote: Just be careful with pg_dump, if you have binary data stored in your 8.4 db. In default mode it just export text. Er ... what? Can you elaborate on that? Do you mean large objects? bytea fields? Something else? -- Craig Ringer

Re: [SQL] extracting location info from string

, and to RAISE NOTICE or to issue a NOTIFY if you need to do closer-to-realtime checking. -- Craig Ringer Tech-related writing at http://soapyfrogs.blogspot.com/ -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] extracting location info from string

to impossible to distinguish it from a country. Not least because some places are both, eg: Luxembourg The Vatican Singapore (The Grand Duchy of Luxembourg has other cities, but still serves as an example). -- Craig Ringer Tech-related writing at http://soapyfrogs.blogspot.com

Re: [SQL] unnest in SELECT

sense to have them in the SELECT list anyway. Few databases support it, and PostgreSQL's behavior is a historical quirk that I think most people here hope will go quietly away at some point. Use unnest in a FROM clause. -- Craig Ringer -- Sent via pgsql-sql mailing list (pgsql-sql

Re: [SQL] column type for pdf file

, you need another way to talk to the server than just the Pg connection, and most importantly your backups become more complicated because you have two things to back up. It's not simple, and it depends a lot on how much the data changes, how big the files are, etc. -- Craig Ringer -- Sent

Re: [SQL] client-side lo_import() provided by libpq ?

On 05/19/2011 04:51 AM, Emi Lu wrote: About client-side lo_import(), is there an online doc about install lo_import? It's in the manual. http://www.postgresql.org/docs/current/static/lo-examplesect.html -- Craig Ringer -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make

Re: [SQL] column type for pdf file

transactions, so you kind of get the best of both worlds. Very cool. If reiser4 hadn't gone the way of the dodo such a thing might've become possible on Linux, but I'm not aware of any other Linux file systems that safely support transactions. -- Craig Ringer -- Sent via pgsql-sql mailing

Re: [SQL] Performance of NOT IN and with PG 9.0.4

On 05/16/2011 06:05 PM, Jasmin Dizdarevic wrote: Hi, is there a reason why Not IN-performance is so poor in 9.0.4 compared to 8.4? Example queries? EXPLAIN ANALYZE output? http://wiki.postgresql.org/wiki/SlowQueryQuestions -- Craig Ringer -- Sent via pgsql-sql mailing list (pgsql-sql

Re: [SQL] PosgreSQL - TSQL2 question

On 06/02/11 06:26, Sarbu Anca wrote: Could you please tell me what I need to do to run TSQL2 on PostrgreSQL for Windows? What do I need to install? Where can I found it? How do I do the installation. The temporal support extension is at : http://temporal.projects.postgresql.org/ and is on

Re: [SQL] check files .backup

writing the backup, calculate an md5sum or (preferably) digitally sign the backup using gpg. An md5sum is only really protection against corruption unless you store it somewhere separate and secure. I prefer to digitally sign my backups with detached gpg signatures. -- Craig Ringer -- Sent via

Re: [SQL] check files .backup

On 25/01/11 08:18, manuel antonio ochoa wrote: Do you know if exist a function to check my file.backup created by pgdump. ? What do you want to check? That it restores correctly? That it's complete and not truncated? That it hasn't been modified since being originally written? That it matches

Re: [SQL] find and replace the string within a column

\([^)]+\) (in other words an open parenthisis, then a sequence of one or more of any character other than a close parenthesis, followed by a close parentheis) and replacing with an empty string ? -- Craig Ringer Tech-related writing at http://soapyfrogs.blogspot.com/ -- Sent via pgsql-sql mailing list

Re: [SQL] HowTo divide streetname from house-nr ?

this with free-form addresses using national phone databases, postcode databases, etc. -- Craig Ringer Tech-related writing at http://soapyfrogs.blogspot.com/ -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] Slow response in select

/explain.txt Woah. That's an interesting plan. When concerned about execution time, it's probably best to post EXPLAIN ANALYZE rather than plain EXPLAIN results. EXPLAIN ANALYZE provides more timing information and information about how rowcount estimates differed from reality. -- Craig Ringer

Re: [SQL] Jagged/ragged arrays

On 21/09/2010 3:42 AM, Dmitriy Igrishin wrote: Hey Florian, What do you mean by ragged arrays? At a guess: craig= SELECT '{ {1}, {1,2}, {1}, {1,2,3} }'::integer[][]; ERROR: multidimensional arrays must have array expressions with matching dimensions (OP) Correct? -- Craig Ringer Tech

Re: [SQL]

ditched Access entirely as the user who was demanding the use of MS Access relented (phew!), so I put together a simple web-app to do what they wanted in a day. Hopefully I'll never need to go near ODBC again, because it's a truly special way to talk to PostgreSQL. -- Craig Ringer -- Sent via

Re: [SQL] kind of RULE on SELECT depending on existence of a WHERE condition

parameters into existing SQL using `EXECUTE USING'. Possibly-null params can be handled using COALESCE or CASE to avoid string-building. -- Craig Ringer -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] indexes

update only the mat.view entries they know are affected by a given update/insert/delete. -- Craig Ringer -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] [GENERAL] DataBase Problem

the connection was closed. The error message its self gives you the next step. I suggest reading this: http://wiki.postgresql.org/wiki/Guide_to_reporting_problems for some hints in case you need to ask a more detailed follow-up. -- Craig Ringer -- Sent via pgsql-sql mailing list (pgsql-sql

Re: [SQL] Using || operator to fold multiple columns into one

On 24/12/2009 5:04 AM, Rosser Schwarz wrote: On Wed, Dec 23, 2009 at 1:44 AM, Craig Ringer cr...@postnewspapers.com.au wrote: SELECT COALESCE( t_number::text, t:string::text, t_date::text, t_boolean::text) AS value; Your invocation of COALESCE is incorrect -- it is n-ary, but it returns

Re: [SQL] Using || operator to fold multiple columns into one

) AS value; Also: You do have a CHECK constraint on the table that asserts that at most one of those entries may be non-null, right? If not, you're very likely to land up with entries with more than one t_something non-null sooner or later so I suggest adding one. -- Craig Ringer -- Sent via

Re: [SQL] short-cutting if sum()constant

require a just-in-time compiler akin to what Java uses, though the ability to compile once and cache would help get rid of some of the complexity of Java's. It'd quickly become attractive to just use PL/Java instead, or write your own C-language function and LOAD it. -- Craig Ringer -- Sent via

Re: [SQL] Profiling tool for postgres under win32

worker thread, and keep the UI responsive. (Doesn't make sense for web apps, but is important for normal GUI apps). - Get faster disks, more RAM for caching, etc. -- Craig Ringer -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http

Re: [SQL] Profiling tool for postgres under win32

. -- Craig Ringer -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] Why don't I get a LATIN1 encoding here with SET ENCODING?

Bryce Nesbitt wrote: Craig Ringer wrote: In truth, that's how I'd expect it to happen. If I ask for the byte 0xfd in a string, I don't want the server to decide that I must've meant something else because I have a different client encoding. If I wanted encoding conversion, I wouldn't have

Re: [SQL] Why don't I get a LATIN1 encoding here with SET ENCODING?

Bryce Nesbitt wrote: Craig Ringer wrote: Yes - but you are *not* presenting a Latin-1 character. You're presenting four Latin-1 characters: '\', '3', '7', '5' Well, then I have a different question. If I can view a bytea column as so: select object from context_objects where

Re: [SQL] Why don't I get a LATIN1 encoding here with SET ENCODING?

Bryce Nesbitt wrote: Craig Ringer wrote: Yes - but you are *not* presenting a Latin-1 character. You're presenting four Latin-1 characters: '\', '3', '7', '5' Well, then I have a different question. If I can view a bytea column as so: select object from context_objects where

Re: [SQL] @@Error equivalent in Postgresql

SQLException that in turn propagates up to code that's in a position to do something about the problem. -- Craig Ringer -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] Random Unique Id

://wiki.postgresql.org/wiki/Pseudo_encrypt thanks to the incredibly helpful folks on this list, in this case particularly Daniel Verite. -- Craig Ringer -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] Hibernate, web application and only one sequence for all primary keys

is pretty good, and covers this sort of thing well. -- Craig Ringer -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] Multiple simultaneous queries on single connection

to use explicitly declared cursors and FETCH to interleave requests for results from one or more queries in the same transation using the one connection, but only one FETCH may be active at a time. -- Craig Ringer -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes

Re: [SQL] Bit by commands ignored until end of transaction block again

for a rollback would give you a ROLLBACK TO SAVEPOINT if the transaction is a subtransaction. -- Craig Ringer -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] Bit by commands ignored until end of transaction block again

(); commit; and a standalone: select my_function(); in both cases the statement executes in a transaction, and in both cases individual statements within the function are within the same transaction. That's why any function can EXCEPTION blocks, etc, which rely on savepoints. -- Craig Ringer -- Sent

Re: [SQL] Bit by commands ignored until end of transaction block again

app-level transaction APIs that contain and manage the DB-level ones anyway. RELEASE SAVEPOINT would only COMMIT the transaction *if* the savepoint that it's releasing started it. So, what you're really asking for boils down to nestable transactions? -- Craig Ringer -- Sent via pgsql-sql

Re: [SQL] uniqueness constraint with NULLs

it to be NULL for some rows. Oh. Er, In that case, the partial unique index is your best bet (but 'a' and 'b' should ne NOT NULL, right). in case I am missing some other solution that doesn't involve the use of triggers etc. Sometimes a trigger is the right solution. -- Craig Ringer -- Sent via

Re: [SQL] uniqueness constraint with NULLs

more blocks, so the index will be bigger and more data will have to be read in to satisfy a `where c is not null' constrained query. So a partial index isn't _as_ good as partitioning the data - but it's quite a bit easier. -- Craig Ringer -- Sent via pgsql-sql mailing list (pgsql-sql

Re: [SQL] backup and restore

will thread your message under a now-unrelated thread. Compose a new message instead. -- Craig Ringer -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] backup and restore

Craig Ringer wrote: ... something kinda rude, in retrospect. Sorry. Unpleasantness is going around in my immediate environment, and I'm apparently prickly and grumpy. -- Craig Ringer -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http

[SQL] select regexp_matches('a a a', '([a-z]) a','g');

expressions. Handily, recent PostgreSQL versions support these, so you can write: test= select regexp_matches( 'a a a', '([a-z]) (?=a)', 'g'); regexp_matches {a} {a} (2 rows) -- Craig Ringer -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your

Re: [SQL] using a list to query

, ',') AS x ); ... but you should consider storing your list in an array instead, or using a more conventional child table with a (pkid, refid) pair list. -- Craig Ringer -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org

Re: [SQL] Performance problem with row count trigger

, t_begin, t_end, ctime and mtime fields do not change almost as often as the assoc_count field, split them into a separate table with a foreign key referencing dataset_id, rather than storing them directly in the dataset table. -- Craig Ringer -- Sent via pgsql-sql mailing list (pgsql-sql

Re: [SQL] dynamic OUT parameters?

-cursors.html#AEN40465 Personally, I find it difficult to imagine what could be wrong with that. -- Craig Ringer -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] dynamic OUT parameters?

call your dynamic reporting function. 2) Can i make a special type on_the_fly and returning setof that_type? You're better off using SETOF RECORD, at least in my opinion. -- Craig Ringer -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http

Re: [SQL] dynamic OUT parameters?

have to do any special work to call the function, and you can (with most DB access APIs) FETCH records from the cursor rather conveniently. See: http://www.postgresql.org/docs/8.3/static/plpgsql-cursors.html -- Craig Ringer -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make

Re: [SQL] How much the max image size can be inserted into Postgresql

section of the MANUAL is: http://www.postgresql.org/docs/current/static/largeobjects.html If those assumptions are invalid or insufficient, perhaps you could provide a more complete description of how you're doing things? -- Craig Ringer -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org

Re: [SQL] Sequence and nextval problem

the ODBC driver options you'll want. -- Craig Ringer Private Sub Form_BeforeInsert(Cancel As Integer) 'Assign a primary key, since Access isn't smart enough to retrieve the 'database-generated one (or ODBC provides no generic mechanism for doing so). If Not IsNull(Me.booking_id

Re: [SQL] UPDATE and DELEte with a lot of register is to slow...

or DELETE takes a long time, or that it slows down other queries? -- Craig Ringer -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] [PERFORM] Can we activate WAL runtime?

no argument at all why it would be safe to flip it on-the-fly. Again, though, that may be new in 8.3, I really would wait for some confirmation. -- Craig Ringer -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] EXISTS

) but the current approach does work fine. It also helps that you can often achieve the required logic with plain, standard SQL. The CASE statement is particularly useful: SELECT CASE WHEN col1 = 'mystring' THEN [expression or function call] END FROM Table; -- Craig Ringer -- Sent via

Re: [SQL] Multi-line text fields

to the first newline would instead probably be the fastest. It'd also let you easily strip the trailing \r if any was present. -- Craig Ringer -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] Special grouping on sorted data.

is different create a new row. I suspect that Crystal Reports may be pulling the whole data set from PostgreSQL then doing its processing client-side. Try turning on query logging in the server and running your report. See what SQL Crystal Reports actually executes. -- Craig Ringer -- Sent via pgsql

Re: [SQL] pg_restore in java connection (auto_commit = false)

this per-table dump and restore hassle at all. -- Craig Ringer -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] more than 1000 connections

a pool of connection or something. Yes, I'd say so. What is preventing you from trying that? What is your question? -- Craig Ringer -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] more than 1000 connections

have to guess that. -- Craig Ringer -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] Function returning setof taking parameters from another table

generate_series(1,10) as x; -- Craig Ringer -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] PERSISTANT PREPARE (another point of view)

, so that doesn't mean much. Tom Lane responded to that post to point out some of the complexities: http://archives.postgresql.org/pgsql-hackers/2008-04/msg00868.php -- Craig Ringer -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http

Re: [SQL] PERSISTANT PREPARE (another point of view)

or SELECT function(params). It's pretty clear that there are some tricky aspects though, what with schema search paths, role priveleges, etc. -- Craig Ringer -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref

Re: [SQL] How to find space occupied by postgres on harddisk

. It's not like a 5GB dump will take all that long to load. -- Craig Ringer -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] Update and trigger

for the execute No, at present you must create a function that returns TRIGGER and then use that as the target to execute. At least as far as I know. -- Craig Ringer -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org

Re: [SQL] design resource

and printer drivers. - Craig Ringer -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] Trouble with exception

was fairly expensive. -- Craig Ringer -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] design resource

ISBNs. I wouldn't be at all surprised to see books on addressing alone. -- Craig Ringer -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] Extremely Low performance with ODBC

to use the index, especially if there are also values for `somethingelse' that occur a lot. Try running your query in psql/pgadmin using PREPARE and EXECUTE and see if you get the same result. -- Craig Ringer -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes

Re: [SQL] Extremely Low performance with ODBC

. That might really help with tracking down issues that appear to only happen with queries run by an app, or though a particular interface. Can it be done? Or is the DB server not capable of generating explain output (say to a log) and also returning a resultset? -- Craig Ringer -- Sent via

Re: [SQL] Substract queries

want is a query that returns all records in the first query EXCEPT those returned by the second query, then see: http://www.postgresql.org/docs/8.3/static/queries-union.html -- Craig Ringer -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http

Re: [SQL] merge timestamps to intervals

there was a conversation in pgsql-sql with subject Difference in columns that included examples that can be trivially adapted to your problem. -- Craig Ringer -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] Difference in columns

doesn't give you the first record with zero difference; it returns only true differences. Here's one possible way to add your initial record: SELECT a.ts, b.size - a.size AS diff FROM x_temp a, x_temp b WHERE b.id = a.id + 1 OR (b.id = (SELECT min(id) FROM x_temp) AND a.id = b.id); -- Craig Ringer

Re: [SQL] Difference in columns

a refcursor for a query result set to iterate over, or pass it parameters to constrain the query with a WHERE clause. The former is more flexible, the latter is easier to use. -- Craig Ringer -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http

  1   2   >