Re: [GENERAL] My function run successfully with cursor, but can't change table

ss any display of the error with the WHEN OTHERS block. -- Kevin Grittner EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- 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] Strange behavior of "=" as assignment operator

onsistent with SQL. > > I think we should just document it and move on. +1 -- Kevin Grittner EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- 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] Insert with query

Adrian Klaver wrote: > On 06/01/2013 06:47 AM, Kevin Grittner wrote: >> Currently on an AFTER ... FOR EACH ROW we fire the trigger once >> *for* each affected row, that's true.  But we don't do it >> immediately after the *triggering event* -- we do it immedi

Re: [GENERAL] Insert with query

Adrian Klaver wrote: > On 05/31/2013 08:15 AM, Kevin Grittner wrote: >> Adrian Klaver wrote: >>> On 05/31/2013 06:32 AM, Kevin Grittner wrote: >>> But why? The OP specified FOR EACH ROW in the trigger >>> statement. >> >> I went to the SQL s

Re: [GENERAL] Insert with query

Adrian Klaver wrote: > On 05/31/2013 06:32 AM, Kevin Grittner wrote: >> Juliano Amaral Chaves wrote: >> >>> By doing insert into a table using a query, it seems that all >>> records of consultation were included bypassing the AFTER INSERT >>> triggers a

Re: [GENERAL] Insert with query

state after the INSERT statement completes, >   rather than the state after the insert of the individual row ] I can see where that would be confusing, but things are operating as designed, AFAICS.  The trigger fires after the statement, not after each affected row is processed.

Re: [GENERAL] 9.3 beta and materialized views

for use in REFRESH. >> > Ah, right. Makes sense. It will also be used to drive incremental update (in future releases), as it does in every other product with materialized views that I know of.  In 9.3 we only got as far as explicit REFRESH, but I hope to improve on that in 9.4. -- Kevin

Re: [GENERAL] Large amount of serialization errors in transactions

-based connection pooler (like pgbouncer configured in transaction mode) is currently your best defence against hitting the wall on this issue. -- Kevin Grittner EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-general mailing list (pgsql-general@postgr

Re: [GENERAL] In what situations do you use -U of initdb?

y OS login except as an identifiable person, and then we could track who was logged in when and what they ran through sudo. -- Kevin Grittner EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org)

Re: [GENERAL] Large amount of serialization errors in transactions

number of database connections.  This tends to help in general, but is especially important when using serializable transactions. http://wiki.postgresql.org/wiki/Number_Of_Database_Connections -- Kevin Grittner EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company

Re: [GENERAL] Normal errors codes in serializable transactions

r test in a transaction indicated that an operation should be able to succeed and then it fails on the constraint.  Such cases normally indicate concurrency issues, not any bug in PostgreSQL or necessarily even in the application. -- Kevin Grittner EnterpriseDB: http://www.enterprisedb.com The Enter

Re: [GENERAL] DISTINCT ON changes sort order

sagekind DESC,     msgid; If that doesn't give you what you're after, we need a better explanation of what you want.  A self-contained test case, where you create a table and load it with data and show a query, would be best. -- Kevin Grittner EnterpriseDB: http://www.enterprise

Re: [GENERAL] Log messages regarding automatic vacuum and exclusive locks

the table.  It steps aside to avoid holding up the other process, and is just making a note of that.  If the table is being repopulated, it is probably just as well that autovacuum does not truncate the table, since that would just result in reallocation as rows are added. -- Kevin Grittner Enterpris

Re: [GENERAL] pg_stop_backup running for 2h10m?

François Beausoleil wrote: > xlogs aren't being added to the backup directory. Any clue in the server log why that is? -- Kevin Grittner EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org)

Re: [GENERAL] Regression test fails v9.2.4

ild environment. I can't help being a little curious why you are overriding these defaults. -- Kevin Grittner EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscri

Re: [GENERAL] False unique constraint violation (exception block)

ttp://wiki.postgresql.org/wiki/Guide_to_reporting_problems -- Kevin Grittner EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pg

Re: [GENERAL] Queries seldomly take >4s while normally take <1ms?

e cause? Probably not. -- Kevin Grittner EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- 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] Inconsistent query performance

to the write glut problem than versions which are still in support.  It's hard to even suggest what steps to take next without knowing the OS, your hardware, or your configuration.  Please read these two pages: http://www.postgresql.org/support/versioning/ http://wiki.postgresql.org/wiki/

Re: [GENERAL] pg_stat_get_last_vacuum_time(): why non-FULL?

g other things, that means that index-only scans will cease to work until the table has a normal vacuum.  A normal vacuum (or autovacuum) will restore those, so a VACUUM FULL should probably set things up to show that the table is in need of a vacuum soon. -- Kevin Grittner EnterpriseDB: http://www.ent

Re: [GENERAL] BEFORE UPDATE trigger doesn't change column value

Kevin Grittner wrote: > Your BEFORE UPDATE trigger could leave the "synced" value in NEW > alone if force_sync was false, and set "synced" to false > otherwise.  It could then set NEW.force_sync to false, to leave you > ready for the next update. Sorry, that&#

Re: [GENERAL] BEFORE UPDATE trigger doesn't change column value

gger could leave the "synced" value in NEW alone if force_sync was false, and set "synced" to false otherwise.  It could then set NEW.force_sync to false, to leave you ready for the next update. -- Kevin Grittner EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Co

Re: [GENERAL] Syntax problem with INDEX on expression

test (((xpath('//rms:xsid/text()',udh,array[array['rms','http://www.example.com']]))[1]::text)); -- Kevin Grittner EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- 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] BEFORE UPDATE trigger doesn't change column value

e specified in the SET clause of the UPDATE; a column which is omitted from that clause will look exactly the same as a column which is set to the value it already had. -- Kevin Grittner EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-general

Re: [GENERAL] upgrading from V8.3.4 to V9.2.4

Robert Treat wrote: > Yeah, there were also some subtle breakage around keywords used > as variable naming when plpgsql was port to use the core lexer. One more: from a Java client access to bytea columns will break if you don't also use the latest JDBC driver jar. -- Kev

Re: [GENERAL] PostgreSQL: CPU utilization creeping to 100%

David Noel wrote: > On 4/2/13, Kevin Grittner wrote: >> David Noel wrote: >> >>> 'select * from pg_stat_activity' shows that the queries are not >>> waiting, and are in the idle state. >> >> The process is idle or the process is running

Re: [GENERAL] PostgreSQL: CPU utilization creeping to 100%

David Noel wrote: > 'select * from pg_stat_activity' shows that the queries are not > waiting, and are in the idle state. The process is idle or the process is running the query?  If the latter, what do you mean when you say "the queries ... are in the idle state

Re: [GENERAL] in C trigger function find out if column is part of primary key

his bit of code: http://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=contrib/tcn/tcn.c;h=eb7e1a61a6a1d5c5ed2e840af41410ac4a52418f;hb=master#l127 -- Kevin Grittner EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-general mailing list (pgsql-general@p

Re: [GENERAL] ts_tovector() to_query()

t > will do this?  Or am I forced to sift through the results of the > initial query after the fact? SELECT m.* FROM movies m   WHERE to_tsvector(m.item_title) @@ to_tsquery('Robocop & (DVD | Collection)') -- Kevin Grittner EnterpriseDB: http://www.enterprisedb.com The Enterpr

Re: [GENERAL] pg_stat_get_last_vacuum_time(): why non-FULL?

r years of fixes for bugs and security vulnerabilities.  There is a very good chance that any problem you see already fixed and you are just choosing to run without the fix. -- Kevin Grittner EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-general ma

Re: [GENERAL] bloating index, pg_restore

ithin one cluster, or what? Without more detail, we can only guess. -- Kevin Grittner EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- 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] Why does Postgres allow duplicate (FK) constraints

ndexing. > > I can understand this for indexes, but a foreign key constraint does not > create > one. I once saw a case where this needed to be done because the dependency information somehow became inconsistent. -- Kevin Grittner EnterpriseDB: http://www.enterprisedb.com The Enter

Re: [GENERAL] pg_stat_get_last_vacuum_time(): why non-FULL?

database in good shape.  Also, a VACUUM FULL is an extreme form of maintenance which should rarely be needed; if you find that you need to run VACUUM FULL, something is probably being done wrong which should be fixed so that you don't need to continue to do such extreme maintenance. --

Re: [GENERAL] Rewritten rows on unchanged values

a new value than to not generate the UPDATE in the first place.  There is a trigger function to do this extra work for those who need it; take a look at the suppress_redundant_updates_trigger() function: http://www.postgresql.org/docs/current/interactive/functions-trigger.html

Re: [GENERAL] "Leaking" disk space on FreeBSD servers

acuum had been turned off and the instance was just coming up on the point where wraparound prevention runs were about to be triggered. pg_clog was where most of the wasted space was. No guarantees that this is the issue, but it sounded similar -- Kevin Grittner EnterpriseDB: http://www.ente

Re: [GENERAL] DB design advice: lots of small tables?

Shaun Thomas wrote: > On 03/15/2013 08:36 AM, Kevin Grittner wrote: > >> I occasionally hear someone maintaining that having a meaningless >> sequential ID column as the primary key of each table is required >> by the relational model. > > You know, I've heard y

Re: [GENERAL] big un stacking query - help save me from myself

where you have a literal in the code?  If so, what is the point of using MAX?  Could you create a sample "stacked" table, insert about 10 rows, and show the SELECT which would give the "unstacked" form? -- Kevin Grittner EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- 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] DB design advice: lots of small tables?

it often allows complex queries to be much better optimized, since they aren't forced through a single navigational linkage. -- Kevin Grittner EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- 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] big un stacking query - help save me from myself

h is important, too; but if you make the issue easier to understand, the odds improve that someone will volunteer the time needed to make a suggestion. -Kevin -- Kevin Grittner EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-general mailing list (

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

stgresql.git;a=commit;h=b19e4250b45e91c9cbdd18d35ea6391ab5961c8d This fix was backpatched as far as 9.0 and is present in the latest minor releases, but not earlier ones.  It can cause symptoms such as you describe. -- Kevin Grittner EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgr

Re: [HACKERS] [GENERAL] Floating point error

2943093776702880859375 Of course, some values can't be precisely written in decimal with so few digits. -- Kevin Grittner EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To m

Re: [GENERAL] Floating point error

7;1'::real);  ?column? --  t (1 row) select '.1'::real::float;   float8    ---  0.10001490116 (1 row) -- Kevin Grittner EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- 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] Dumb question involving to_tsvector and a view

the results of the function which generated the tsvector, versus a GIN index on the stored tsvector.  In our case, a typical scan for document text against years of accumulated court documents was about 300 ms versus about 1.5 seconds.  It may matter that we weren't just looking for matches,

Re: [GENERAL] Need help extripating plpgsql

Adrian Klaver wrote: > On 02/22/2013 11:59 AM, Kevin Grittner wrote: >> Adrian Klaver wrote: >> >>> At this point I am not sure how to do this with out creating role >>> that has superuser privileges. >> >> Something like this?: >> >> -

Re: [GENERAL] Dumb question involving to_tsvector and a view

similar situation and benchmarked it both ways.  For my situation I came out ahead writing the extra column for inserts and updates than generating the tsvector values on the fly each time it was queried.  YMMV.  It probably depends mostly on the ratio of inserts and updates to selects. -- Kev

Re: [GENERAL] Need help extripating plpgsql

stgres update pg_database set datistemplate = true   where datname = 'template2'; checkpoint; -- Create a user who can own the database and plpgsql. create user bob with createdb; set role bob; create database bob template template2; \c bob create extension plpgsql; -- Kevin Grittner En

Re: [GENERAL] Database corruption event, unlockable rows, possibly bogus virtual xids? Invalid backend server xid

  It sounds like things were left in this state for a very long time, which can lead to all kinds of problems, notably bloat and blocking. > I do still have the old data directories so I can start them up > and check out the dataset. Any advice? I would start it up and see what'

Re: [GENERAL] Full text and removing dashes from names

ector, then concatenating that tsvector with what came from the lexeme/dictionary evaluation. -- Kevin Grittner EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscri

Re: [GENERAL] Why is this a cross join?

e as a result given that data set. -- Kevin Grittner EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- 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] Why is this a cross join?

first six" value, and 20 rows in the other table with that same "first six" value, how do you want them to be matched up?  Do you want one row in the result for every row in one of the tables?  If so, how do you want to determine which of the matching rows in the other table

Re: [GENERAL] PG9.2.3. Query hanging: SELECT count(*) FROM pg_catalog.pg_class...

50002c...@gw.wicourts.gov -- Kevin Grittner EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- 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] Runtime variations during day

abase. Well, if the resources are split among more processes, each process will take longer to run.  No surprise there.  Of course, there could be some interaction -- queries which do more work because statistics are out of date could contribute to the slowdown of everything else

Re: [GENERAL] Runtime variations during day

40GB and the machine has 64GB. http://www.postgresql.org/message-id/cajnnue1x6yyqkbvdqkmtonk62gzsph-edpr4u7+lot66m3s...@mail.gmail.com -- Kevin Grittner EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-general mailing list (pgsql-general@pos

Re: [GENERAL] Runtime variations during day

mory 64GB If you haven't already done so, I would use settings something like this in postgresql.conf: effective_cache_size = 32GB seq_page_cost = 0.1 random_page_cost = 0.1 cpu_tuple_cost = 0.03 -- Kevin Grittner EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company

Re: [GENERAL] ERROR: relative path not allowed for COPY to file

Andrew Taylor wrote: > postgres=# COPY post_e_n > postgres-# TO 'usr/local/psql/csv/post_e_n.csv' > postgres-# WITH DELIMITER ',' > postgres-# CSV HEADER; > ERROR:  relative path not allowed for COPY to file I think you need a slash at the front of that path. -Kevin -- Sent via pgsql-genera

Re: [GENERAL] Cache lookup failed for relation

David Clymer wrote: > The SERIALIZABLE isolation mode is being used in 9.0, and > REPEATABLE READ in 9.2, which should be the same thing, correct > (eg. 9.0 serializable ~ 9.2 repeatable read)? Correct. In 9.0 SERIALIZABLE and REPEATABLE READ are exactly same.  In 9.1 and later REPEATABLE READ

Re: [GENERAL] Can you create aliases in the psql shell?

Modulok wrote: > Is there a way to create command aliases in the psql shell? I can never > remember all the \d* commands and have to look them up every time. If I > could > create things like \list_databases, \list_tables, \list_roles, etc, > it would be > much easier for me to remember. > >

Re: [GENERAL] no implicit cast error in 9.2?

Adrian Klaver wrote: > On 02/08/2013 12:23 PM, AI Rumman wrote: >> I got a bit confused after installing this version. So far I used to >> know that from Postgresql 8.3 implicit casting has been removed and the >> following should not work at 8.3 : >> create table testtab ( id varchar, id1 int) >

Re: [GENERAL] Decrease the time required function

Albe Laurenz wrote: > Karel Riverón wrote: >> I have a PL/pgSQL function that it takes 4 seconds to execute. >> OPEN casos_reales; >> LOOP FETCH casos_reales into cr; > [...] >> OPEN criterios; >> LOOP FETCH criterios into c; > [...] >> SELECT c_r_c.id, valor INTO crc >>

Re: [GENERAL] Visual query builder for PosgreSQL?

Satoshi Nagayasu wrote: > Of course, I can write ad-hoc queries by myself. However, I'd > like to allow non-tech people to issue ad-hoc queries with using > some visual query builder. You should probably take a look at http://htsql.org/ It is free open source software intended for "accidental p

Re: [GENERAL] REINDEX deadlock - Postgresql -9.1

Anoop K wrote: >I will try. Here are the gdb stacktraces of hung processes. > > > Have you tried `kill -SIGTERM` on the "idle in transaction" pid? -Kevin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/ma

Re: [GENERAL] Unusually high IO for autovacuum worker

Vlad Bailescu wrote: > Because of our application stack (Hibernate ORM) we use a > before_insert trigger to insert rows into the right partition and > in the master table and then an after_insert trigger to delete > them from master table (we need the original insert to return the > inserted row

Re: [GENERAL] Pg & Tcl - is it dying out?

Carlo Stonebanks wrote: > "Are we seeing the demise of PG & Tcl? Should I advise my client > to NOT upgrade any systems running Tcl apps and stick with legacy > OS's?" > > Not the demise of PG in general, but specifically of concern for > the PG & Tcl developers. Your best bet to sort out the an

Re: [GENERAL] Optimizing select count query which often takes over 10 seconds

Alexander Farber wrote: > The cronjob gives me now occasionally: > > /* reset and then update medals count */ > update pref_users set medals = 0; > psql:/home/afarber/bin/clean-database.sql:63: ERROR:  deadlock detected > DETAIL:  Process 31072 waits for ShareLock on transaction 124735679; blocke

Re: [GENERAL] Optimizing select count query which often takes over 10 seconds

Alexander Farber wrote: > Kevin Grittner wrote: >> Alexander Farber wrote: >> >>> update pref_users set medals = 0; >>> UPDATE 223456 >> >> You're probably going to like your performance a lot better if >> you modify that to: >> &g

Re: [GENERAL] Optimizing select count query which often takes over 10 seconds

Alexander Farber wrote: > update pref_users set medals = 0; > UPDATE 223456 You're probably going to like your performance a lot better if you modify that to: update pref_users set medals = 0 where medals <> 0; -Kevin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To

Re: [GENERAL] database design best pratice help

Jose Soares wrote: > In my db I have about one hundred tables like this: > > code > description > > To avoid to have a so great number of similar tables in the db > I wonder if it is a good idea to unify all these tables in one > big table like this: > > id > code > table_ name > description > C

Re: [GENERAL] Best approach for query with optional constraints

Jon Smark wrote: > Here's the problem: I want to retrieve a list of bugs (possibly) matching > certain constraints.  One possible constraint is a user ID: if given, only > those bugs reported by the user will be returned.  Another constraint is > a set of tags: only those bugs that contain *all*

Re: [GENERAL] Range for user-defined SQLSTATE codes

Ian Pilcher wrote: > I can't be the first person (or even the 10,000th) to want to define my > own SQLSTATE codes when raising errors in a stored procedure.  I've > just tested doing so in a PL/pgSQL function access via JDBC, and I had > no problem retrieving the non-standard state from the SQLEx

Re: [GENERAL] Postgresql error

MarkB wrote: > I have written a program where 2 computers are connected to the same > database. The first PC executes an update statement and then sends a > notification. This makes the second PC execute a select statement on the > same table. The second PC then gets an error: > > 'Field "Fieldna

Re: [GENERAL] Running update in chunks?

Jeff Janes wrote: > one hstore field can easily be equivalent to 50 text fields with > an index on each one. > > I'm pretty sure that that is your bottleneck. I agree that seems like the most likely cause. Each update to the row holding the hstore column requires adding new index entries for all

Re: [GENERAL] Restore 1 Table from pg_dumpall?

Rich Shepard wrote: > Is there a way I can extract a single table's schema and data from the > full backup? If so, I can then drop the fubar'd table and do it correctly > this time. If you have a server with enough free space, you could restore the whole cluster and then selectively dump what you

Re: [GENERAL] Pg_xlog increase due to postgres crash (disk full)

Cliff de Carteret wrote: > I have now deleted the copy on the remote wal_archive folder and the > archiving is now functioning and sending the logs from the local to the > remote folder. The remote database does not startup and the following is in > the log: > > LOG: database system was shut down

Re: [GENERAL] Pg_xlog increase due to postgres crash (disk full)

[Please keep the list copied, and put your reply in-line instead of at the top.] Cliff de Carteret wrote: > On 22 January 2013 16:07, Kevin Grittner wrote: > >> Cliff de Carteret wrote: >> >>> The current setup has been working successfully for several years >>

Re: [GENERAL] Pg_xlog increase due to postgres crash (disk full)

Cliff de Carteret wrote: > The current setup has been working successfully for several years > until the recent database crash What file does the server log say it is trying to archive? What error are you getting? Does that filename already exist on the archive (or some intermediate location used

Re: [GENERAL] Yet Another Timestamp Question: Time Defaults

Adrian Klaver wrote: > If I was following Gavan correctly, he wanted to have a single > timestamp field to store calender dates and datetimes. In other > words to cover both date only situations like birthdays and > datetime situations like an appointment. If that is actually true, it sounds like

Re: [GENERAL] Running update in chunks?

Kevin Grittner wrote: > update imports set make_id = 0 > > Query returned successfully: 98834 rows affected, 45860 ms execution time. For difficult problems, there is nothing like a self-contained test case, that someone else can run to see the issue. Here's a starting point: cr

Re: [GENERAL] Yet Another Timestamp Question: Time Defaults

Adrian Klaver wrote: > I see where my confusion lies. There are two proposals at work in the above: > > "Taking another tangent I would much prefer the default time to be > 12:00:00 for the conversion of a date to timestamp(+/-timezone)" > > "Propose: '2013-12-25'::timestamp ==> 2013-12-25 12:0

Re: [GENERAL] Running update in chunks?

Kevin Grittner wrote: > First off, what does it say for rows affected? (Hint, if you really > are using a default configuration and it doesn't say 0 rows > affected, please show us the actual query used.) Never mind that bit -- I got myself confused. Sorry for the noise. -Kevin

Re: [GENERAL] Case insensitive collation

Scott Marlowe wrote: > Honestly as a lazy DBA I have to say it'd be pretty easy to write a > script to convert any unique text index into a unique text index with > a upper() in it. As another poster added, collation ain't free > either. I'd say you should test it to see. My experience tells me >

Re: [GENERAL] Running update in chunks?

Tim Uckun wrote: > If you have any suggestions I am all ears. For the purposes of this > discussion we can narrow down the problem this update statement. > > Update imports set make_id = null. Well, that simplifies things. First off, what does it say for rows affected? (Hint, if you really are

Re: [GENERAL] Running update in chunks?

Richard Huxton wrote: > On 21/01/13 20:09, Tim Uckun wrote: >> Just to close this up and give some guidance to future >> googlers... > Careful, future googlers. +1 >> Conclusion. Updates on postgres are slow > Nope. Agreed. >> (given the default postgresql.conf). I presume this is due to >>

Re: [GENERAL] pg_Restore

Adrian Klaver wrote: > On 01/21/2013 08:46 AM, bhanu udaya wrote: >> Can we achieve this template or pg_Restore in less than 20 >> minutes time. > Seems to me this is where Point in Time Recovery(PITR) might be > helpful. Maybe, if the source is on a different set of drives, to reduce contention

Re: [GENERAL] Running update in chunks?

Richard Huxton wrote: > The only differences I can think of are WAL logging (transaction > log) and index updates (the temp table has no indexes). What about foreign keys? Are there any tables which reference the updated column in a foreign key declaration? Do they have indexes on that column? -

Re: [GENERAL] not(t_xmax = 0)

Alejandro Carrillo wrote: > this function didn't work to know if a row can surely dead? > > http://doxygen.postgresql.org/tqual_8c_source.html#l01236 Sure, as long as you call it after HeapTupleSatisfiesMVCC(), as the comment specifies. Also note that not all deleted or updated tuples will be re

Re: [GENERAL] not(t_xmax = 0)

Alejandro Carrillo wrote: > De: Kevin Grittner >> Please give a high-level description of what you are trying to >> accomplish and why. > I try to do a function that let know which rows are deleted in a > table using the xmax !=0 That's not something you're

Re: [GENERAL] not(t_xmax = 0)

Alejandro Carrillo wrote: > I need to filter xid != 0. I tried this "not(t_xmax = 0)" but I > dont sure that this work ok > > How I do it? Please give a high-level description of what you are trying to accomplish and why. I didn't find it at all clear from your sample function, -Kevin -- Sen

Re: [GENERAL] Combine Date and Time Columns to Timestamp

Rich Shepard wrote: > On Fri, 18 Jan 2013, Adrian Klaver wrote: > >> test=> SELECT ('2012-10-29 '||'10:19')::timestamp; >> timestamp >> - >> 2012-10-29 10:19:00 > >  Thanks, Adrian. I suspected it was simple but I could not find a reference > to the syntax. Of course, since y

Re: [GENERAL] Temp table's effect on performance

Robert James wrote: > What information would be helpful to post? That question comes up so often we have a page to help answer it.  :-) http://wiki.postgresql.org/wiki/SlowQueryQuestions -Kevin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subs

Re: [GENERAL] SELECT DISTINCT

salah jubeh wrote: > During my work, I have seen a common practice of using DISTINCT. > Some will argue that developer should know the effect of using > it, but keep in mind not all developers are gurus in RDBMs. "SELECT DISTINCT eliminates duplicate rows from the result." Personally, I would not

Re: [GENERAL] String comparison and the SQL standard

Tom Lane wrote: > This probably has more to do with what these systems think the > data type of an undecorated literal is, than with whether they do > trailing-space-insensitive comparison all the time. I suspect so. Keep in mind that PostgreSQL does not comply with the standard in this regard, b

Re: [GENERAL] [postgis-users] Query with LIMIT but as random result set?

Stefan Keller wrote: > "... ORDER BY random() LIMIT 10;" works ok. > > But with the following option it gets more tricky assume: >> And as an option the (limited) resultset should be spatially >> distributed (not clustered). > > I'm thinking about some radial spatial distribution function. So,

Re: [GENERAL] When to run Vacuum in postgres 9.0.4

[resending after completion -- the prior send was accidental] Anjali Arora wrote: > I have a large dataset and I have crawled it two and three times; > hence reltuples has increased. So I need some way to find out > vacuum is needed on the system. > > Please help me in finding out some paramete

Re: [GENERAL] When to run Vacuum in postgres 9.0.4

Anjali Arora wrote: > I have a large dataset and I have crawled it two and three times; hence > reltuples has increased. So I need some way to find out vacuum is needed on > the system. > > Please help me in finding out some parameter or statistics which will tell me > vacuum is required on t

Re: [GENERAL] Large number of rows in pg_type and slow gui (pgadmin) refresh

Igor Neyman wrote: > Thomas Kellerer wrote: >> Why do you need so many types? > Probably those are not the types Robert created explicitly. > There must be lots of tables/views (m.b. lots of partitions) in > the database. Every table/view adds couple records to pg_type: > one type for table/view

Re: [GENERAL] Permission for relationship but not for select is possible?

Edson Richter wrote: > I would like to have two schemas: > > MyDB.sales > MyDB.security > > Users that have rights in sales schema should be able to > select/insert/update/delete. The same users must have rights to check > foreign keys against users table (but they are now allowed to execute

Re: [GENERAL] Question on Trigram GIST indexes

ERR ORR wrote: > Specifically, I was trying to replicate what is done in this blog post: > http://www.postgresonline.com/journal/archives/212-PostgreSQL-9.1-Trigrams-teaching-LIKE-and-ILIKE-new-tricks.html > Queries which use "WHERE "TST_PAYLOAD" LIKE 'SEAT%'" go to the btree index > as it should

Re: [GENERAL] Coalesce bug ?

David Johnston wrote: > Understood (I'm guessing there is no "global" cache but simply the > plan-level cache that gets populated each time?) > > However, in the following example the ps3(2) expression should also qualify > for this "folding" and thus the RAISE NOTICE should also appear during pl

Re: [GENERAL] Coalesce bug ?

David Johnston wrote: > I thought that in order to call the Coalesce function the system > would have to know the value of all parameters. There is no lazy > instantiation in SQL. Tom already addressed the main question, but I want to clarify this point. COALESCE is not a function; it is defined

Re: [GENERAL] Moving a database to a new TABLESPACE in Postgres 8.3

Robert James wrote: > In Postgres 8.3, how can I move a database to a different TABLESPACE? > My goal is to move rarely used databases off of the SSD and onto the > HDD. http://www.postgresql.org/docs/8.3/interactive/sql-createtablespace.html http://www.postgresql.org/docs/8.3/interactive/sql-al

Re: [GENERAL] pg_xlog is getting bigger

AI Rumman wrote: > Kevin Grittner wrote: >> AI Rumman wrote: >>> Kevin Grittner wrote: >>>> AI Rumman wrote: >>>> >>>>> I am working on a Postgresql 9.0 server. I have no replication and >>>>> archive mode setup. But I found

<    1   2   3   4   5   6   >