Re: [GENERAL] Insert query hangs
On Wed, Jul 9, 2014 at 1:25 PM, M Tarkeshwar Rao wrote: > > I am sharing the table structure. When we removed the unique index it is > working fine. > And when created normal index(not unique) it is working fine. > > After removing unique index we tried to recreate it but it is giving > following infinite logs : > > concurrent insert in progress within table "eventlogentry" > > caveat when building a unique index concurrently is that the uniqueness > constraint Hum, that is interesting. So to sum up, your database works fine as long as the index isn't present but you want to enforce this unique constraint. What version of Postgres is this precisely (9.1.what?) Has it ever been upgraded using pg_upgrade? Has it suffered a crash or been restored from backups? What of Adrian Klaver's question about the database being copied? When you run create index you get this error? Please paste the actual command and actual error messages. It should contain more information than that. Try doing it with this set first: \set VERBOSITY 'verbose' When you're getting these errors please run select * from pg_stat_activity and paste that information -- remove any confidential information but leave any INSERT, UPDATE, DELETEs on the table you're indexing with just the confidential data removed. Also, I don't think this is right but perhaps it could be a prepared transaction? Try "select * from pg_prepared_xacts"? If it's really corruption --which still seems a bit odd to me-- You might find a simple vacuum or vacuum freeze on the table fixes up the problem. -- greg -- 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 query hangs
On Wed, Jul 9, 2014 at 11:34 AM, M Tarkeshwar Rao wrote: > > We analysed one more thing when we removed the unique index from the table it > is working fine. > Is there any issue in indexing? > > Is there any option to repair the table or its indexing? REINDEX is useful for indexes. >From the sounds of it you had an uncommitted transaction that had inserted a conflicting id. If you were able to drop the index the other transaction had committed (or aborted) by that time. It's possible there was corruption but that doesn't usually lead to a hang and you haven't provided enough information to think that was the case. -- greg -- 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] Sorting with materialized paths
On Sun, May 9, 2010 at 4:47 PM, Tom Lane wrote: > Ovid writes: >> My apologies. This isn't PG-specific, but since this is running on >> PostgreSQL 8.4, maybe there are specific features which might help. >> I have a tree structure in a table and it uses materialized paths to allow >> me to find children quickly. However, I also need to sort the results >> depth-first, as one would expect with threaded forum replies. > > I think contrib/ltree might help you here. However, it seems to sort > node names textually rather than numerically, so you might need to > change it a bit for your own purposes. > That's rather unfortunate. Ltree is awfully convenient and it would be nice to be able to use it. If you just used plain Postgres arrays of integers you would get the sorting you want. But you lose all the useful ltree operators for trees. -- greg -- 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] Cannot read block 348938 of pdbsynchtable
On Thu, Apr 8, 2010 at 7:12 AM, Utsav Turray wrote: > Even if If i try to pad the file 25205.3 using DD command I am not able to > calculate the bytes to be padded as the total count of the blocks is comming > out to be 521228 and the error is coming cannot read the 348938 block. Assuming 25205 is the right file for this table then block 348938 would be in 25205.2 and be bytes 694,352kB - 694,360kB. try dd if=25205.2 of=/tmp/data bs=8192 skip=$(( 348938 - 2 * 128 * 1024)) count=1 see if you get the same error. If you do and you're happy to zero out the bad block, deleting any data on it you could do something like: dd if=/dev/zero of=25205.2 bs=8192 seek=$(( 348938 - 2 * 128 * 1024)) count=1 -- greg -- 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] pgreplay log file replayer released
On Wed, Mar 17, 2010 at 2:06 PM, Albe Laurenz wrote: > I announce the first release of pgreplay, version 0.9.0 (Beta). > > Project home page: http://pgreplay.projects.postgresql.org/ > > pgreplay reads a PostgreSQL log file (*not* a WAL file), > extracts the SQL statements and executes them in the same order > and relative time against a PostgreSQL database cluster. Do you have a multi-threaded model that tracks which transactions each query belonged to and runs them concurrently like they were in the original setup? That's what I've been looking for. -- greg -- 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] has_schema_privilege function
On Tue, Mar 9, 2010 at 10:28 AM, Jignesh Shah wrote: > Could you tell me is there any other robust way to make sure that user1 > doesn't have CREATE permissions on mydb schema? It depends what you're worried about. If you're worried that plperl will begin mapping booleans to perl variables differently or Postgres will change the text representation then you could alter the SQL to say something like CASE WHEN has_schema_privilege() THEN 1 ELSE 0 END or whatever constant you prefer like 'yes' and 'no' or 'ok' and ''. -- greg -- 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] autovacuum question
On Tue, Mar 9, 2010 at 1:47 PM, Scot Kreienkamp wrote: > I found a way to do it very easily using LVM snapshots and WAL log > shipping, but the net effect is I'm bringing a new LVM snapshot copy of > the database out of recovery every 1-2 hours. That means I'd have to > spend 15 minutes, or one-quarter of the time, doing an analyze every > time I refresh the database. That's fairly painful. The LVM snap and > restart only takes 1-2 minutes right now. Your snapshot should have the same stats that the server does, so this doesn't actually seem to explain the discrepancy. You be running into performance problems with LVM if the snapshot is the one paying the price for all the CoW copies. Or it could be that doing retail block copies as needed results in them being fragmented and destroying the sequential scan performance. You might be able to reduce the difference by making sure to do a vacuum and a checkpoint immediately prior to the snapshot. That would hopefully achieve setting most hint bits so that read-only queries on the snapshot don't cause writes to blocks just to set them. There might be an option in LVM to materialize the entire snapshot which might be able to bring the performance up to the same level and hopefully allocate all the blocks sequentially. -- greg -- 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] Scratching my head why results are different between machines.
On Thu, Mar 4, 2010 at 2:14 PM, Justin Graf wrote: > To pretty much anyone outside MS, a sane human would think 64 bit apps > in SysWoW64 and 32Bit apps in System32. :'( > Ah, but you all are forgetting that the "32" here is to distinguish it from the default odbc interface which as i recall was a *16* bit interface. I'm not sure what exactly that means but as a result the 32-bit odbc configuration is entirely separate from the "regular" odbc configuration. It's not the instruction set that the dll uses it's which set of shared data structures it uses and which api it provides. -- greg -- 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] Hung postmaster (8.3.9)
We should probably also check and prohibit including directories as files. On Tuesday, March 2, 2010, Tom Lane wrote: > In the meantime, it seems like we ought to take two defensive steps: -- greg -- 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] Implementing an Index Access Method in PG 8.4
On Tue, Feb 23, 2010 at 10:00 AM, Carsten Kropf wrote: > I have a question according to the implementation of a new index access > method in Postgres. Is it necessary to implement a new resource manager for > XLog when I am trying to achieve a stable new index access method? > It's not currently possible to register a new recovery manager for a module built outside the Postgres source tree. What's happened in the past is new index methods weren't recoverable (after a database crash indexes had to be rebuilt) but when they were integrated into the Postgres source tree adding recoverability was a major piece of that integration. There's been some talk about allowing modules to register new recovery managers but in the past it gets stuck on where to store information about the recovery manager since the database tables aren't available. And on how to guarantee that the backup database and the original database have the same idea of which recovery manager is which. -- greg -- 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] Sorting performance vs. MySQL
On Tue, Feb 23, 2010 at 9:48 AM, Yeb Havinga wrote: > Greg Stark wrote: >> >> You can do \set FETCH_COUNT to have psql use a cursor automatically. >> > > It seems like a big win in this case. What would be the downside of having a > fetch_count set default in psql? They were mentioned previously in this thread: 1) If an error occurs partway through the execution of the query you might receive part of the result set. 2) psql won't be able to align the columns properly -- greg -- 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] Sorting performance vs. MySQL
On Tue, Feb 23, 2010 at 6:48 AM, Scott Marlowe wrote: >> I'm relieved that Postgresql itself does not, in fact, suck, but >> slightly disappointed in the behavior of psql. I suppose it needs to >> buffer everything in memory to properly format its tabular output, >> among other possible reasons I could imagine. > > It's best when working with big sets to do so with a cursor and fetch > a few thousand rows at a time. It's how we handle really big sets at > work and it works like a charm in keeping the client from bogging down > with a huge memory footprint. > You can do \set FETCH_COUNT to have psql use a cursor automatically. -- greg -- 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] DDL trigger kind functionality in PostGreSQL
On Thu, Feb 18, 2010 at 4:55 AM, dipti shah wrote: > Hi, > > I was looking for SQL DDL trigger kind of functionality in PostGreSQL but > couldn;t find any. There isn't any. > Basically I want to make sure that no users > should use "DROP" command directly on my database even though he/she owner > of table or any database object. I want users to use stored procedures > always to drop or alter the tables and any other database objects. You could revoke their access to drop the objects and create SECURITY DEFINER functions which do the drops. Then only grant execute access to the people who should have had access to drop the objects in the first place. -- greg -- 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] Truncate and delete adds wal logs for slave to process.
On Thu, Feb 11, 2010 at 5:47 PM, Chris Barnes wrote: > > Because both truncate and delete, I would think that this action would be > put into the pg_log as a log file that can be rolled back. And, when > complete, it would be shipped to the standby to be processed? > > To reduce this logging, shipping and processing would it be smarter to have > the tables dropped and recreated? Truncate will generate about the same amount of log data as dropping and creating the table. Delete will generate quite a bit more, but still much less than 31G. It will also leave you needing to vacuum and reindex more often. -- greg -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Best way to handle multi-billion row read-only table?
On Wed, Feb 10, 2010 at 2:32 PM, Asher wrote: > The data will initially be accessed via a simple GUI which will allow > browsing over a subset of the data (subsampled down to 1 sample/minute/hour, > etc. It sounds like you could use a tool like rrd that keeps various levels of aggregation and intelligently chooses the right level for the given query. I think there are such tools though I'm not sure there are any free ones. -- 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] Re: 8.5devel: unexpected and illogical error during transaction, but transaction don't rolled back
On Wed, Feb 10, 2010 at 6:59 AM, A. Kretschmer wrote: > test=*# analyse table_a; > ERROR: canceling autovacuum task > CONTEXT: automatic vacuum of table "test.public.table_a" > ANALYZE > Time: 1235,600 ms > > > I think, that's not an ERROR, just a NOTICE for me. And yes, the > transaction isn't rolled back, so it isn't an error. Did you start the server from the same terminal? I think the ERROR and CONTEXT line come from the server, not psql and are expected behaviour. -- greg -- 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] Multiple buffer cache?
I doubt pinning buffers ever improve system on any halfway modern system. It will often *look* like it has improved performance because it improves the performance of the queries you're looking at -- but at the expense of slowing down everything else. There is a use case it would be useful for though. When you have some queries that are latency critical. Then you might want to pin the buffers those queries use to avoid having larger less urgent queries purge those buffers. If we had a way to mark latency critical queries that might be a more flexible interface but ewe would need some way to control just how critical they are. we wouldn't want to keep those buffets pinned forever. greg On 7 Feb 2010 07:36, "Tom Lane" wrote: Greg Smith writes: > ... Part of the reason this feature > hasn't been been perceived as more valuable is because just letting the > two cache levels involv... Or to put it even more clearly: the only way that pinning particular tables into RAM could beat letting the caching algorithm take care of it is if the caching algorithm is seriously suboptimal. Therefore, it would make more sense to put development effort into improving the cache algorithm than to put it into giving the DBA a rather blunt instrument for overriding the cache algorithm. We've already made several rounds of improvements of that kind, and are quite happy to consider more. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subs...
Re: [GENERAL] surprised by non-strict array_append
If it were strict wouldn't it return NULL?
greg
On 3 Feb 2010 07:16, "J. Greg Davidson" wrote:
I was caught out today by the non-strict behavior of array_append
causing me to get an undesired result for a COALESCE. My subsequent
attempt to create a STRICT VARIADIC generalization of array_append
led to another surprise. The problem was easily solved, but might
be of interest to others. Perhaps someone will enlighten me as to
why the standard array functions are non-strict and why the STRICT
VARIADIC function fails to be strict. I've boiled it down to make
it clear:
psql (8.4.2)
SELECT COALESCE( ARRAY[1] || NULL::integer, ARRAY[42] );
coalesce
--
{1,NULL}
(1 row)
SELECT COALESCE( array_append( ARRAY[1], NULL), ARRAY[42] );
coalesce
--
{1,NULL}
(1 row)
CREATE OR REPLACE
FUNCTION array_add(ANYARRAY, VARIADIC ANYARRAY) RETURNS ANYARRAY AS $$
SELECT $1 || $2
$$ LANGUAGE sql STRICT;
COMMENT ON FUNCTION array_add(ANYARRAY, ANYARRAY)
IS 'a strict generalization version of array_append';
SELECT COALESCE( array_add( ARRAY[1], NULL, NULL), ARRAY[42] );
coalesce
---
{1,NULL,NULL}
(1 row)
-- hmm - not very strict!
CREATE OR REPLACE
FUNCTION array_add1(ANYARRAY, ANYELEMENT) RETURNS ANYARRAY AS $$
SELECT $1 || $2
$$ LANGUAGE sql STRICT;
COMMENT ON FUNCTION array_add1(ANYARRAY, ANYELEMENT)
IS 'a strict version of array_append';
SELECT COALESCE( array_add1( ARRAY[1], NULL), ARRAY[42] );
coalesce
--
{42}
(1 row)
-- ah, finally!
_Greg
--
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] Questions about connection clean-up and "invalid page header"
On Mon, Jan 25, 2010 at 1:16 PM, Herouth Maoz wrote: > Well, I assume by the fact that eventually I get an "Unexpected end of file" > message for those queries, that something does go in and check them. Do you > have any suggestion as to how to cause the postgresql server to do so > earlier? No, Postgres pretty intentionally doesn't check because checking would be quite slow. If this is a plpgsql function looping you can put a RAISE NOTICE in the loop periodically. I suppose you could write such a function and add it to your query but whether it does what you want will depend on the query plan. -- greg -- 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] Questions about connection clean-up and "invalid page header"
On Mon, Jan 25, 2010 at 11:37 AM, Herouth Maoz wrote: > The tcp_keepalive setting would only come into play if the remote > machine crashed or was disconnected from the network. > > > That's the situation I'm having, so it's OK. Crystal, being a Windows > application, obviously runs on a different server than the database itself, > so the connection between them is TCP/IP, not Unix domain sockets. The unix socket api is used for both unix domain sockets and internet domain sockets. The point is that in the api there's no way to find out about a connection the other side has closed except for when you write or read from it or when you explicitly check. > And > furthermore, that was exactly the problem as I described it - the fact that > the third party software, instead of somehow instructing Crystal to send a > cancel request to PostgreSQL, instead just kills the client process on the > Windows side. Killing the client process doesn't mean the machine has crashed or been disconnected from the network. I'm assuming Crystal isn't crashing the machine just to stop the report... And even if it did and tcp_keepalives kicked in the server *still* wouldn't notice until it checked or tried to read or write to that socket. -- greg -- 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] Questions about connection clean-up and "invalid page header"
On Mon, Jan 25, 2010 at 8:15 AM, Scott Marlowe wrote: >> Is there a parameter to set in the configuration or some other means to >> shorten the time before an abandoned backend's query is cancelled? > > You can shorten the tcp_keepalive settings so that dead connections > get detected faster. > This won't help. The TCP connection is already being closed (or I think only half-closed). The problem is that in the Unix socket API you don't find out about that unless you check or try to read or write to it. The tcp_keepalive setting would only come into play if the remote machine crashed or was disconnected from the network. -- greg -- 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 Tue, Jan 19, 2010 at 4:41 AM, Craig Ringer wrote: > 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? Well if you use mv to replace the old file with the new one then it should be safe. Unless your query involves opening the table multiple times or your transactions are more complex than a single query... -- greg -- 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
Re: [GENERAL] postgres external table
On Mon, Jan 18, 2010 at 2:57 PM, 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 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
Re: [GENERAL] Collate order on Mac OS X, text with diacritics in UTF-8
On Thu, Jan 14, 2010 at 6:15 AM, Craig Ringer wrote: >>> Out of interest: Why not? >> >> There's plenty of discussion in the archives about it, but basically >> ICU would represent a pretty enormous dependency and would lock us in >> to having no other backend encoding but UTF8. > > Thanks. You're right - I should've just STFA ;-) so I appreciate the > brief explanation. There's also the question of whether being inconsistent with the rest of the system is really the right thing at all. If a programmer does a sort in the database and then writes application code using the same collation on the same system which depends on the data being sorted it's nice that that works. Or if an admin presorts the data using sort before doing a data load he might reasonable expect the table to be effectively clustered. 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. -- greg -- 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] WEIRD! postmaster: segfault with sub select??!
On Sun, Jan 3, 2010 at 8:31 AM, Reto wrote: > Hi everybody, > > I'm facing a strange problem with a relatively simple sub select > whereas everything else runs perfect on this machine (PG 8.4.2 @ > Fedora 12, Core2 E4600, 4GB, 2 x 320GB). > > # SELECT DISTINCT name FROM bbr_parts WHERE id IN (SELECT part_id FROM > bbr_v_office_to_parts WHERE office_id=39 AND office_type=9); > > server closed the connection unexpectedly > This probably means the server terminated abnormally > before or while processing the request. > The connection to the server was lost. Attempting reset: Failed. Look at the postmaster logs for the error which caused the crash. There should be something there even if it seg faulted. Also, can you get a backtrace? Even with your current build if you attach to the backend that you're connected to and get a backtrace that could be useful. (run "select pg_backend_pid()" and then attach to that pid in gdb, type "c" and then after the crash run "backtrace"). The most useful would be to get a coredump from a debugging build but the two things above would be a good start. -- greg -- 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] DELETE ERROR: tuple concurrently updated
On Tue, Dec 29, 2009 at 9:41 AM, Михаил Кечинов wrote: > When I try to delete one row from database (for example): > delete from document where numdoc = 901721617 > I have this error: > ERROR: tuple concurrently updated > SQL state: XX000 > I know, that no one deleting this row at same time. > What's mean this error? So this error can only come from a normal SQL-level delete if there is associated TOAST data which is being deleted as well. In which case that TOAST data must be already marked deleted -- which shouldn't be possible. It sounds like you have a database where some writes from earlier transactions reached the database and others didn't. That can happen if you take an inconsistent backup (without using pg_start_backup()) or if the drive you're using confirmed writes before crashing but didn't actually write them. You might be able to get somewhat further by reindexing the TOAST table for this table. To do so do "REINDEX TABLE document". But note that you could run into further errors from the missing toast data. -- greg -- 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] DELETE ERROR: tuple concurrently updated
On Tue, Dec 29, 2009 at 9:41 AM, Михаил Кечинов wrote: > One week ago our database has crashed and after restore begins some > problems. What version? And how was this backup taken? It sounds like it might be an inconsistent backup. -- greg -- 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] date_trunc on date is immutable?
On Fri, Dec 25, 2009 at 1:58 AM, Scott Marlowe wrote: > Isn't it the client timezone and not the system timezone that actually > sets the tz the tstz is set to on retrieval? It's the GUC: stark=> set timezone = 'America/Los_Angeles'; SET stark=> select now(); now --- 2009-12-25 06:44:33.238187-08 (1 row) I'm not sure if and how we initialize the GUC on a new connection though. It might vary depending on the driver you use too. -- greg -- 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] date_trunc on date is immutable?
On Fri, Dec 25, 2009 at 12:56 AM, Scott Marlowe wrote:
> On Thu, Dec 24, 2009 at 4:36 PM, Kian Wright
> wrote:
>> I'm trying to create an index on the month and year of a date field (in
>> 8.3), and I'm getting the "functions in index expression must be marked
>> IMMUTABLE" error message.
>
> If applied to a timestamp, it is immutable. If it's a timestamp with
> timezone it's not, because the timezone can change, which would change
> the index.
Put another way, a given point in time doesn't necessarily lie in a
particular month or on a particular day because it depends what time
zone the system is set to. So right now it's a day earlier or later in
part of the globe.
To do what you want define the index on date_trunc('month',
appl_recvd_date at time zone 'America/Los_Angeles') or something like that.
You'll have to make sure your queries have the same expression in them
though :( It won't work if you just happen to have the system time
zone set to the matching time zone.
--
greg
--
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] logtrigger/denyaccess triggers removed from master/slave
On Fri, Dec 18, 2009 at 6:59 PM, tamanna madaan wrote: > I am using postgres-8.1.2 and slony-1.1.5 for replication. > I don't know about your Slony problems but the current bug-fix release for 8.1 is 8.1.19. That's 17 releases to fix security holes, crashes, data corruption bugs, etc that you're missing. Skimming through them I don't see any that are obviously related to your problem but it's a lot and I didn't read through them carefully. -- greg -- 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] Catastrophic changes to PostgreSQL 8.4
On Thu, Dec 3, 2009 at 8:33 AM, Craig Ringer wrote: > While true in theory, in practice it's pretty unusual to have filenames > encoded with an encoding other than the system LC_CTYPE on a modern > UNIX/Linux/BSD machine. > > I'd _very_ much prefer to have Bacula back my machines up by respecting > LC_CTYPE and applying appropriate conversions at the fd if LC_CTYPE on > the fd's host is not utf-8 and the database is. a) it doesn't really matter how uncommon it is, backup software is like databases, it's supposed to always work, not just usually work. b) LC_CTYPE is an environment variable, it can be different for different users. c) backup software that tries to fix up the data it's backing up to what it thinks it should look like is bogus. If I can't trust my backup software to restore exactly the same data with exactly the same filenames then it's useless. The last thing I want to be doing when recovering from a disaster is trying to debug some difference of opinion between some third party commercial software and postgres/bacula about unicode encodings. > (3) As (2), but add a `bytea' column to `path' and `filename' tables > that's null if the fd was able to convert the filename from the > system LC_CTYPE to utf-8. In the rare cases it couldn't (due to > reasons like users running with different LC_CTYPE, nfs volumes > exported to systems with different LC_CTYPE, tarballs from > systems with different charsets, etc) the raw unconverted bytes > of the filename get stored in the bytea field, and a mangled > form of the name gets stored in the text field for user display > purposes only. That's an interesting thought. I think it's not quite right -- you want to always store the raw filename in the bytea and then also store a text field with the visual representation. That way you can also deal with broken encodings in some application specific way too, perhaps by trying to guess a reasonable encoding. An alternative would be to just store them in byteas and then handle sorting and displaying by calling the conversion procedure on the fly. -- greg -- 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] postgre...@fosdem 2010 - Call for talks
On Thu, Dec 3, 2009 at 10:48 AM, Dave Page wrote: > We will have a number of 45 minutes slots, and may split one or more > into 3 back-to-back 15 minute slots if we receive suitable proposals. I would like to suggest we reduce the number of talks and have instead some more participatory round-table discussions. I think it would be better to find out what problems people are facing, what features they would be interested in, what's stopping non-users from adopting postgres, etc. I would suggest having a few general topics like "security", "replication", "high availability", "change management", "postgres upgrades", etc. -- greg -- 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] Postgresql8.4 install breaks Evolution on Ubuntu 9.10
On Sun, Nov 29, 2009 at 4:17 PM, Magnus Hagander wrote: > On Sun, Nov 29, 2009 at 16:18, Sachin Srivastava > wrote: >> Apart from libxml2 (which is now being fixed) all other libraries you >> mentioned , dint get installed (or copied) to the PGHOME/lib directory if >> the same name library already present in the system (/lib and /usr/lib). > > What happens if they are installed by the packaging system later on? > Won't that cause a conflict then? Or if the user later uninstalls those libraries -- which can happen automatically when nothing in the packaging system depends on them any longer. But i don't see what the conflict is if they're installed in PGHOME/lib as long as the installer doesn't fiddle with /etc/ld.so.conf or set any environment variables. The binaries should just be built with an rpath pointing to that directory or ship with a startup script which puts that directory in LD_LIBRARY_PATH. Whether you want to append, leaving the system directories ahead of the one-click installed libraries, or prepend so the linker always uses your libraries would depend on how you want it to behave. Setting rpath is equivalent to prepending I believe. -- greg -- 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] pg_attribute.attnum - wrong column ordinal?
On Wed, Nov 25, 2009 at 2:00 AM, Konstantin Izmailov wrote: > Greg, > this is brilliant - thank you very much! > > Is "partition by" compatible to PostgreSQL 8.0/8.2? I could not find > compatibility information. It works fine with PG 8.3/8.4 and Greenplum 3.3 > thou. It's 8.4 only. You could also just generate them on the client since the ordering the query requests is the right order for it. -- greg -- 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] pg_attribute.attnum - wrong column ordinal?
On Wed, Nov 25, 2009 at 1:03 AM, Konstantin Izmailov wrote: > My question: can pg_attribute.attnum be used to determine the sequential > ordinal positions of columns in a table? What is a right way to get the > ordinal numbers? You could use something like: row_number() over (partition by T.schemaname,T.viewname order by attnum) as "ORDINAL_POSITION" If you just stick this in there in place of attnum it'll cause an extra sort. It should be possible with enough clever rearranging of the query to do the whole query with a single sort since that's the same sort order that the results are ordered in. Incidentally you probably want UNION ALL rather than UNION in the original query. -- greg -- 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] safelly erasing dirs/files
On Sat, Nov 14, 2009 at 7:42 PM, Joao Ferreira gmail wrote: > vacuum/reindex is saying: I can't do it cause I have no space :( Hm, vacuum shouldn't require any extra space. I suppose you need enough space for the transaction log though. You can probably get away with a pretty small amount of extra space. Try removing older stuff from /var/log/ to make some extra space or using tune2fs -m (or tunefs -m on bsd i think) to lower the reserved percentage then run vacuum. reindex will require a lot of extra space though. Better is probably to create a new tablespace on a new filesystem and do ALTER TABLE SET TABLESPACE and ALTER INDEX SET TABLESPACE to the new tablespace. Then CLUSTER the table in the new tablespace to compact it. Cluster needs enough space to hold the old and new table at the same time but when it's done it'll have compacted both the table and the indexes better than vacuum does. -- greg -- 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] Incremental Backups in postgres
On Tue, Nov 10, 2009 at 11:03 AM, Alban Hertroys wrote: > IMHO The simplest solution is to just write a dump to the same file every > now and then and have the backup software take care of storing only the > differences. It does have a few drawbacks; it means you'll have a file about > as large as your database on your filesystem just for making backups and > there is a risk that your backup software kicks in before the dump has > finished writing. > > As others mentioned, you can also go with a PITR solution, which is probably > prettier but is a bit harder to set up. It's always worth having the dump, even if you also implement PITR. The dump allows you to restore just specific tables or to restore onto a different type of system. The PITR backup is a physical byte-for-byte copy which only works if you restore the whole database and only on the same type of system. -- greg -- 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] virtualidx exclusive lock
On Mon, Nov 9, 2009 at 7:38 AM, Uwe Schroeder wrote: > What I noticed is when I look at pg_locks, pretty much all of the processes > being idle in transaction have an exclusive lock of locktype "virtualidx". It's "virtualxid" as in "virtual transaction id" and hopefully more than pretty much all have a lock of this type -- *all* transactions start with a lock on their own transaction id and hold it until they finish. That's how other transactions wait for a transaction to finish, by attempting to get a lock on the transaction id of the transaction they're waiting on. -- greg -- 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] sudoku in an sql statement
On Sun, Nov 1, 2009 at 3:19 PM, Tom Lane wrote: > I think the Oracle guy's version could easily be adapted to PG 8.4 --- > those little rownum subqueries seem to be just a substitute for not > having generate_series(1,9), and everything else is just string-pushing. > Don't have time to try it myself right now, though. Interestingly the first thing I ran into when I started doing so was that apparently Oracle *doesn't* handle the lack of the RECURSIVE attribute properly. It still put the common table expressions in scope for the subsequent common table expressions even without the RECURSIVE keyword, which is apparently just a noise word in Oracle. -- greg -- 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] R-Trees in PostgreSQL
On Mon, Nov 2, 2009 at 4:41 PM, Jeff Davis wrote: > On Tue, 2009-11-03 at 00:25 +0100, Viktor Rosenfeld wrote: >> I'd like to know what kind of functions I have to implement for a R-Tree >> index on numeric columns, > > NUMERIC is scalar, so an R-Tree doesn't make much sense. You can install > btree_gist (a contrib module) to be able to use numeric columns as part > of a GiST index. It sounds like what you're trying to do needs an "expression index" so you can construct a data type which does support gist indexes out of your two numeric columns. You could do something like create index i on (point(col1,col2)) though I think you might have to actually make a "box" instead. Alternatively you could look at the "cube" contrib module. As far as i know all of these actually work with doubles though, so you'll lose precision. -- greg -- 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] Help with postgresql memory issue
On Wed, Oct 28, 2009 at 1:05 PM, Brooks Lyrette wrote: > The machine is running a moderate load. This is running on a Solaris Zone. > > Memory: 32G phys mem, 942M free mem, 76G swap, 74G free swap > > PID USERNAME LWP PRI NICE SIZE RES STATE TIME CPU COMMAND > 5069 postgres 1 52 0 167M 20M sleep 0:04 13.50% postgres Hm, well 400 processes if each were taking 190M would be 76G. But that doesn't really make much sense since most of the 167M of that process is presumably the shared buffers. What is your shared buffers set to btw? And your work_mem and maintenance_work_mem? Fwiw ENOMEM is documented as "There is not enough swap space.". Perhaps you have some big usage spike which uses up lots of swap and causes postgres to start needing lots of new processes at the same time? -- greg -- 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] contrib/plantuner - enable PostgreSQL planner hints
On Fri, Oct 16, 2009 at 10:04 AM, decibel wrote: > Out of curiosity, did you look at doing hints as comments in a query? I'm > guessing you couldn't actually do that in just a contrib module, but it's > how Oracle handles hints, and it seems to be *much* more convenient, because > a hint only applies for a specific query Fwiw Oracle is moving away from this now. At OpenWorld the optimizer folks were pleading with folks to get rid of all their hard-coded hints by preparing plans for hinted queries and loading those as the approved plans. In 11g there's a system which ensures the database will not run any plan that isn't approved. In fact it looks an *awful* lot like the system I sketched out 6 years ago in: http://article.gmane.org/gmane.comp.db.postgresql.devel.general/11385 -- greg -- 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] Errors regarding transporting database using pg_dump
On Mon, Oct 5, 2009 at 7:34 AM, Martijn van Oosterhout wrote: > That said, why are you doint this anyway. A better solution may be to > install a trusted language (like plperlu or plpython) and do the system > call from there. > If you just want system(3) you might as well use plsh... -- 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] Re: PostgreSQL reads each 8k block - no larger blocks are used - even on sequential scans
On Sun, Sep 27, 2009 at 11:18 AM, Sam Mason wrote: > On Sun, Sep 27, 2009 at 06:05:51PM +0200, Gerhard Wiesinger wrote: >> A google research has shown that Gregory Stark already worked on that issue >> (see references below) but as far as I saw only on bitmap heap scans. > > Greg Stark's patches are about giving the IO subsystem enough > information about where the random accesses will be ending up next. > This is important, but almost completely independent from the case > where you know you're doing sequential IO, which is what you seem to be > talking about. FWIW I did work to write code to use FADV_SEQUENTIAL and FADV_RANDOM but couldn't demonstrate any performance improvement. Basically Postgres was already capable of saturating any raid controller I could test doing a normal sequential scan with 8k block sizes and no special read-ahead advice. -- greg -- 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] How useful is the money datatype?
2009/10/3 Grzegorz Jaśkiewicz : > depending on the countries, etc - keep currencies in 10.4 , or you can > compromise to 10.3 , otherwise you might run into problems with rounding, > etc. Keeping more digits of precision than the application actually can use is more likely to *cause* problems with rounding than solve them. For example, if you calculate interest on a balance (using floating point arithmetic) and then round it to $10.001 and store that in the balance your application will tell the user and your accounting department that they have $10 and their account. But if you do this ten times they'll mysteriously have an extra cent that the accounting department will not be able to account for. To avoid problems like this you must store precisely as many digits as the application requires. No more and no less. Intermediate calculations can be done with more precision or floating point arithmetic but you have to round or truncate before reporting the results and then store precisely the value you reported. -- greg -- 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] How to simulate crashes of PostgreSQL?
On Fri, Aug 28, 2009 at 4:13 AM, Alvaro Herrera wrote: > Maybe we should have another inter-backend signal: when a process gets > ENFILE, signal all other backends and they close a bunch of files each. I wonder if this is a new problem due to the FSM and VM using up extra file handles? -- greg http://mit.edu/~gsstark/resume.pdf -- 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] Tagged types module and varlena changes
On Wed, Aug 26, 2009 at 1:14 PM, Alban Hertroys wrote: >> struct varlena* tv = (struct varlena*)tt_palloc( VARSIZE( datum ) ); >> >> tv->vl_len = VARSIZE( datum ) - sizeof(Oid); >> memcpy( tv->vl_dat, >> &((struct taggedtypev*)DatumGetPointer( datum ))->val, >> VARSIZE(datum) - sizeof(Oid) - VARHDRSZ ); >> return PointerGetDatum( tv ) ; > > > This doesn't compile anymore as the vl_len member of struct varlena no > longer exists and we're supposed to use the SET_VARSIZE macro instead now. I > tried that, but then the memcpy bails out due to the size calculation being > wrong. I don't know enough about varlena usage to figure out what the > correct way of calculating the size for memcpy is, or whether that approach > is at all feasable with the current varlena implementation. What should the > above read? > With the SET_VARSIZE the above should work *as long as datum is not toasted (or packed)*. If it's been detoasted then that's good, or if it was freshly generated and not stored in a tuple then it should be good too. If it's not guaranteed to be detoasted then you probably should be adding detoast calls since the Oid will have alignment requirements. Otherwise you could just use VARSIZE_ANY_EXHDR() It's generally a good thing to rename the vl_len field to something like _vl_len to catch anyplace else that's referring directly to it. That will never work any more, neither for setting it nor for reading it. They all need to be converted to use VARSIZE and SET_VARSIZE I haven't looked at the rest of the code to see if the general approach is still reasonable. The varlena changes shouldn't be fatal though. -- greg http://mit.edu/~gsstark/resume.pdf -- 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] Multiple table entries?
On Mon, Aug 24, 2009 at 2:03 AM, Tom Lane wrote: > Jeff Ross writes: >> Tom Lane wrote: >>> heap_update is broken. Details left as an exercise for the reader > >> Well, as the reader that started this all ;-) should I be worried? >> Should I do a pg_dump and reinstall? Roll back to 8.3.7? Or just >> relax, don't worry and have a sparkling adult beverage? > > Well, it's a pretty bad bug but as far as I can see a simple "VACUUM > table" command should fix it up --- would you confirm? At the very least taking regular pg_dumps is probably wise. That's probably wise even if there aren't Postges bugs though since it's the most flexible type of backup to deal with application bugs with. The answer to whether you should roll back until 8.4.1 comes out will depend on how valuable your data is, how critical the downtime to repair any corruption would be, versus the time that you'll spend on rolling it back. That's a complicated calculus which will be different for every user. The bug found should only affect recovery though. So unless you have a standby slave database or have postgres or system crashes it shouldn't be relevant. -- greg http://mit.edu/~gsstark/resume.pdf -- 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] Multiple table entries?
On Sun, Aug 23, 2009 at 9:06 PM, Jeff Ross wrote:
> pglogd=# select (h).* from (select
> page_header(get_raw_page('pg_namespace',0))
> pglogd(# as h) as x;
> lsn | tli | flags | lower | upper | special | pagesize | version |
> prune_xid
> ---+-+---+---+---+-+--+-+---
> 0/1A15904 | 1 | 5 | 64 | 6944 | 8192 | 8192 | 4 |
> 897
Well this confirms that the bug involves the all-visible flag as the 5
is PD_ALL_VISIBLE + PD_HAS_FREE_LINES
It's been overtaken by events as Tom has spotted a problem already.
--
greg
http://mit.edu/~gsstark/resume.pdf
--
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] Multiple table entries?
On Sun, Aug 23, 2009 at 5:02 PM, Greg Stark wrote: > On Sun, Aug 23, 2009 at 2:18 PM, Jeff Ross wrote: > Incidentally, may as well ask the usual questions: And just for reference, what does pg_controldata print? -- greg http://mit.edu/~gsstark/resume.pdf -- 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] Multiple table entries?
On Sun, Aug 23, 2009 at 7:34 PM, Tom Lane wrote: > Greg Stark writes: >> On Sun, Aug 23, 2009 at 7:00 PM, Tom Lane wrote: >>> But we don't use that while examining individual tuples, do we? > >> We don't use the visibility map itself but we *do* use the page >> header's all visible bit. On a sequential scan we skip the visibility >> check for tuples on the page if the page header bit is set. > > Urgh. I bet that's where the problem is then. Some path is failing > to clear that bit, or maybe there's a race condition that allows it > to become set incorrectly (ie right after somebody else adds a tuple). Well it only gets set in vacuum when we have the vacuum cleanout lock so I can't see it getting set incorrectly unless there's a simple logic error in vacuum. I just scanned it and it looks right to me. I can imagine it failing to be unset. Or being unset but allowing a race condition window before it's unset when it's wrong. That doesn't explain why it the HEAP_ONLY_TUPLE flag would be missing though. Also, vacuum logs a warning if the flag is found to be set and shouldn't be. I suppose that could easily be missed though if it's autovacuum that ran it. Also, the flag might fail to be cleared but then be cleared later by another operation. I wonder how much damage it can do while it's wrong. -- greg http://mit.edu/~gsstark/resume.pdf -- 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] Multiple table entries?
On Sun, Aug 23, 2009 at 7:00 PM, Tom Lane wrote: > Greg Stark writes: >> The last tuple is marked strangely I think. I don't think it's >> supposed to have XMAX_INVALID if xmax is 0 but I could be wrong. Also, >> I don't understand why it's marked as UPDATED if ctid and xmax aren't >> set. > > No, that all looks right to me. UPDATE sets HEAP_UPDATED on the newer > version of the row, not the older one. What looks interesting to me is > that the last update isn't marked HEAP_ONLY_TUPLE, ie, it's not in the > same HOT chain. Why is that I wonder ... > >> I'm wondering if the page allvisible flag is set. The visibility map >> is one of the few 8.4 patches which impact transaction visibility. > > But we don't use that while examining individual tuples, do we? We don't use the visibility map itself but we *do* use the page header's all visible bit. On a sequential scan we skip the visibility check for tuples on the page if the page header bit is set. -- greg http://mit.edu/~gsstark/resume.pdf -- 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] Multiple table entries?
On Sun, Aug 23, 2009 at 6:23 PM, Greg Stark wrote:
> On Sun, Aug 23, 2009 at 5:37 PM, Jeff Ross wrote:
>>
>> pg_clog is 32K. I've put it at http://www.openvistas.net/pg_clog
>
> Sorry, I'm getting a 404
For what it's worth this is what the heap dump shows. I don't
understand why you're seeing these tuples show up if they're all
properly marked as updated and XMAX_COMMITTED.
The last tuple is marked strangely I think. I don't think it's
supposed to have XMAX_INVALID if xmax is 0 but I could be wrong. Also,
I don't understand why it's marked as UPDATED if ctid and xmax aren't
set.
Could you also send
select (h).* from (select page_header(get_raw_page('pg_namespace',0))
as h) as x;
I'm wondering if the page allvisible flag is set. The visibility map
is one of the few 8.4 patches which impact transaction visibility.
lp lp_off flags lp_len t_xmin t_xmax field3 t_ctid t_info2 t_info
3 76161 141 2 897 0 (0,6) 16387 9482
HOT_UPDATED, UPDATED, XMAX_COMMITTED, XMIN_COMMITTED
6 73401 129 897 898 0 (0,7) -16381 9482
HOT_UPDATED, HEAP_ONLY_TUPLE, UPDATED, XMAX_COMMITTED, XMIN_COMMITTED
7 72201 117 898 899 0 (0,9) -16381 9482
HOT_UPDATED, HEAP_ONLY_TUPLE, UPDATED, XMAX_COMMITTED, XMIN_COMMITTED
9 70881 129 899 900 0 (0,10) -16381 9482
HOT_UPDATED, HEAP_ONLY_TUPLE, UPDATED, XMAX_COMMITTED, XMIN_COMMITTED
10 69441 141 900 0 0 (0,10) -32765 10506
UPDATED, XMAX_INVALID, XMIN_COMMITTED
--
greg
http://mit.edu/~gsstark/resume.pdf
--
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] Multiple table entries?
On Sun, Aug 23, 2009 at 5:37 PM, Jeff Ross wrote: > > pg_clog is 32K. I've put it at http://www.openvistas.net/pg_clog Sorry, I'm getting a 404 -- greg http://mit.edu/~gsstark/resume.pdf -- 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] Multiple table entries?
On Sun, Aug 23, 2009 at 2:18 PM, Jeff Ross wrote:
> I had to modify your query slightly to make it run--hope I got what you are
> after!
>
> select (h).* from (select
> heap_page_items(get_raw_page('pg_namespace',0)) as h) as x;
>
> http://www.openvistas.net/pageinspect.html
Incidentally, may as well ask the usual questions:
Did you upgrade this via pg_dump/pg_restore or via pg_migrator?
Are you running with fsync=off?
Are you running with synchronous_commit=off?
Have you had any system crashes?
Have you had any Postgres crashes?
Of the above only fsync=off combined with a system crash ought to be
unsafe, but it could be good to know which other events have occurred
to know which code paths have been exercised.
Do you still have pg_clog/ (that's kept longer than the xlog so I
would expect you would), how large is it? could you send that file? It
has a record of which transactions have committed or aborted, no
private data.
--
greg
http://mit.edu/~gsstark/resume.pdf
--
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] Multiple table entries?
On Sun, Aug 23, 2009 at 4:40 AM, Greg Stark wrote:
> On Sun, Aug 23, 2009 at 4:06 AM, Jeff Ross wrote:
>> Greg Stark wrote:
>
> Yeah, that's a problem. Would you be able to load the pageinspect
> contrib module and run a query?
>
> select (h).* from (select
> heap_page_items(get_raw_page('pg_namespace',0)) as h from p) as x;
Also, do you have the WAL log files going back to the database
creation? If so *please* preserve them! You're the second person to
report similar symptoms so it's starting to look like there may be a
serious bug here. And the nature of the problem is such that I think
it may require someone sifting through the xlog WAL files to see what
sequence of events happened.
The wal files are in a subdirection of the database root called
pg_xlog. If you still have one named 0001 then you
have all of them. Please, if possible, copy them to a backup
directory.
--
greg
http://mit.edu/~gsstark/resume.pdf
--
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] Multiple table entries?
On Sun, Aug 23, 2009 at 4:06 AM, Jeff Ross wrote:
> Greg Stark wrote:
>> Actually, I wonder if this isn't more likely to show the problem -- it
>> would explain why *all* your tables are showing up with duplicates
>> rather than just one.
>>
>> select xmin,xmax,ctid,oid,* from pg_namespace
>
> http://openvistas.net/pg_namespace_query.html
Yeah, that's a problem. Would you be able to load the pageinspect
contrib module and run a query?
select (h).* from (select
heap_page_items(get_raw_page('pg_namespace',0)) as h from p) as x;
--
greg
http://mit.edu/~gsstark/resume.pdf
--
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] join from array or cursor
On Sun, Aug 23, 2009 at 1:30 AM, John DeSoi wrote: > While it avoids the sort of my method, it appears to be almost 5 times > slower (about 4000 keys in the cursor, Postgres 8.4.0): > > > Function Scan on cursor_pk arr (cost=0.00..116011.72 rows=1000 width=4) > (actual time=13.561..249.916 rows=4308 loops=1) > SubPlan 1 > SubPlan 2 > SubPlan 3 > ... Ugh, I guess using a subquery didn't work around the problem of the (r).* getting expanded into multiple columns. This is starting to be a more annoying limitation than I realized. This also means when we do things like select (x).* from (select bt_page_items(...)) or select (h).* from (select heap_page_items(...)) It's actually calling bt_page_items() repeatedly, once for every column in the output record? Bleagh. -- greg http://mit.edu/~gsstark/resume.pdf -- 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] Multiple table entries?
On Sat, Aug 22, 2009 at 9:31 PM, Jeff Ross wrote: > I browsed through the system catalogs but haven't found anything yet that > can shine some light on this. Actually, I wonder if this isn't more likely to show the problem -- it would explain why *all* your tables are showing up with duplicates rather than just one. select xmin,xmax,ctid,oid,* from pg_namespace -- greg http://mit.edu/~gsstark/resume.pdf -- 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] Multiple table entries?
On Sat, Aug 22, 2009 at 9:31 PM, Jeff Ross wrote: > Hi, > > I recently upgraded to 8.4 and everything went great. All databases are > working as they are supposed to, no problems seen. > > Today, however, I did a \d on a database and was surprised to see sets of 5 > identical table entries for each one that is supposed to be there. Ugh. What does select xmin,xmax,ctid,oid,* from pg_class return? -- greg http://mit.edu/~gsstark/resume.pdf -- 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] Error inserting data to bytea column in 8.4
2009/8/21 Andrus Moor : > In 8.4, script > > create temp table test ( test bytea ); > insert into test values(E'\274') Try E'\\274' -- greg http://mit.edu/~gsstark/resume.pdf -- 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] join from array or cursor
On Fri, Aug 21, 2009 at 2:16 PM, Greg Stark wrote:
> On Fri, Aug 21, 2009 at 1:16 PM, John DeSoi wrote:
>>
>> Yes, this is the best I have come up with so far. I have a set returning
>> function which returns the key and the index number. The implementation with
>> a cursor looks like this:
>>
>> SELECT * FROM cursor_pk('c1') c LEFT JOIN foo ON (c.pk = foo.pk) ORDER BY
>> c.idx;
>
> This might be a circumstance where it's really best to just force the
> planner to use a particular plan. There's no way to tell it "the order
> of the array" without forcing an entirely unnecessary sort.
Of course immediately upon hitting send I did think of a way:
SELECT (r).*
FROM (SELECT (SELECT x FROM x WHERE a=id) AS r
FROM unnest(array[1,2]) AS arr(id)
) AS subq;
--
greg
http://mit.edu/~gsstark/resume.pdf
--
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] join from array or cursor
On Fri, Aug 21, 2009 at 1:16 PM, John DeSoi wrote:
>
> Yes, this is the best I have come up with so far. I have a set returning
> function which returns the key and the index number. The implementation with
> a cursor looks like this:
>
> SELECT * FROM cursor_pk('c1') c LEFT JOIN foo ON (c.pk = foo.pk) ORDER BY
> c.idx;
This might be a circumstance where it's really best to just force the
planner to use a particular plan. There's no way to tell it "the order
of the array" without forcing an entirely unnecessary sort.
Something like
SET LOCAL enable_mergejoin = 'off';
SET LOCAL enable_hashjoin = 'off';
SELECT * from unnest(arr) as v(id) join tab using (id)
RESET ALL;
--
greg
http://mit.edu/~gsstark/resume.pdf
--
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] unique index for periods
On Thu, Aug 20, 2009 at 3:14 PM, Tom Lane wrote: > I don't believe it is possible to use a btree index for this purpose, > because there just isn't a way to express "overlaps" as a total order. That's true for the general case of indexing ranges but I don't think that's true for the case where overlaps are illegal. In such a case you could just, sorting by the start point, compare the previous entry's end point with your start point and the next entry with your end point. However that's not the way unique indexes work in Postgres so supporting that would require a lot of new abstractions and code, not just a new opclass. -- greg http://mit.edu/~gsstark/resume.pdf -- 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] Stock Market Price Data & postgreSQL? HELLPPP Please
On Wed, Aug 19, 2009 at 9:22 PM, Sanjay Arora wrote: > - This is Time Series Data (I don't know what that is except that it > relates to data marked/related to time) and not suited to a RDBMS. > - You need it in Esper (a CEP engine used by Marketcetera, an open > source trading platform) which needs to get a data stream, whereas an > RDBMS will return a recordset. > - A RDBMS including postgreSQL simply cannot handle the amount of > real-time data coming in from a stock exchange. > - A RDBMS simply cannot provide data, even if you build streams from > recordsets returned, at the speeds required by any technical analysis > charting program. There are whole companies doing nothing but providing streaming RDBMS used by traders, airline reservation systems, etc. c.f Streambase, and Truviso amongst others. I'm not aware of any open source streaming databaes so for a shoestring budget you're going to be limited to using existing tools. There are certainly people who scale up Postgres to high oltp traffic but it does require time and money as you scale up, TANSTAAFL... I'm sure it can be done, Postgres is very flexible, though whether it's a perfect fit with your needs or not I can't say. You might also consider whether one of the existing streaming database system providers would give you a development license for free or on a trial basis if you hope to be making big money down the road. -- greg http://mit.edu/~gsstark/resume.pdf -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Re: Unit conversion database (was: multiple paramters in aggregate function)
On Wed, Aug 19, 2009 at 8:24 PM, Alvaro Herrera wrote: >> [1] It doesn't correctly convert °C to °F or vv, that was one of the >> first things I tried. > > Seems it's easy to misuse it. You need tempF(x) and tempC notation for > converting absolute temperature differences: > > You have: tempF(212) > You want: tempC > 100 That depends on whether you're converting a temperature or a temperature difference. If you want to know what a 100 degree C drop in temperature equates to in Fahrenheit the answer is not 212 but rather 180. I think it would be useful to have a builtin data type which contained a float and an opaque text unit. It could support linear operations like +, -, and sum() by just throwing an error if the units didn't match. Then you could add an add-on function which converted one such datum to another with a desired new units by calling out to the units program. That would allow people to store values with heterogenous units. So for example you could have SMART stats in a single table where the time values, unitless values, and temperature values are all in the same column. As long as you only compare, say, drive temperatures to max temperatures you never actually need to know about the units. It would serve as an assertion check to ensure you don't compare drive temperatures to error counts or something like that. -- greg http://mit.edu/~gsstark/resume.pdf -- 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] PQgetlength vs. octet_length()
On Tue, Aug 18, 2009 at 6:39 PM, Michael Clark wrote: > But it seems pretty crazy that a 140meg bit of data goes to 1.3 gigs. Does > that seem a bit excessive? From what you posted earlier it looked like it was turning into about 500M which sounds about right. Presumably either libpq or your code is holding two copies of it in ram at some point in the process. 8.5 will have an option to use a denser hex encoding but it will still be 2x as large as the raw data. > I avoided the binary mode because that seemed to be rather confusing when > having to deal with non-bytea data types. The docs make it sound like > binary mode should be avoided because what you get back for a datetime > varies per platform. There are definitely disadvantages. Generally it requires you to know what the binary representation of your data types is and they're not all well documented or guaranteed not to change in the future. I wouldn't recommend someone try to decode a numeric or a postgres array for example. And floating point numbers are platform dependent. But bytea is a case where it seems more natural to use binary than text representation. -- greg http://mit.edu/~gsstark/resume.pdf -- 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] Failover, Wal Logging, and Multiple Spares
On Tue, Aug 18, 2009 at 1:25 AM, Yaroslav Tykhiy wrote: > Encouraged by Bruce Momjian, I tried and had some success in this area. It > was a controlled failover but it worked like a charm. An obvious condition > was that the warm standbys be in perfect sync; you can't do the trick if > some of them received the last WAL segment while the others didn't. It seems like it should be possible to weaken this constraint. As long as you're careful to fail over to the slave which is the furthest ahead in replaying WAL. All the other slaves must switch to replaying logs from the new master before the point where it took over. This does seem like a very useful area to explore. -- greg http://mit.edu/~gsstark/resume.pdf -- 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] Rapid Seek Devices (feature request)
2009/8/17 Jeremy Harris : > Could not pgsql *measure* these costs (on a sampling basis, and with long > time-constants)? In theory, sure. In practice, well, there are some engineering challenges to solve. 1) The cost model isn't perfect so the it's not clear exactly what to measure to get the best result. As the cost model stands now you would have to include some of the cpu time as well as the i/o time. 2) Timing is expensive. On many platforms getting the current time takes a long time and would make a big difference to the runtime of the query. -- greg http://mit.edu/~gsstark/resume.pdf -- 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] ERROR: attempted to delete invisible tuple
On Mon, Aug 17, 2009 at 7:59 PM, Tom Lane wrote: > Greg Stark writes: >> Excluding the cases where our own xid is in the tuple I think the >> relevant cases are either > >> xmin aborted or in progress (or in future) >> MOVED_OFF and xvac committed >> MOVED_IN and xvac aborted or is in progress (or in future) > > Ah. I hadn't bothered to check the code in detail before asking about > the current XID. Given subsequent data, it seems that current XID must > have moved past xvac while we were wondering about it. This could mean > either corrupted xvac values, or that the crash caused current XID to go > backwards (suggesting loss of both the current pg_control and a big > chunk of WAL). Since multiple tuples on different pages were involved, > I'm inclined to believe the second theory. I would think xmin would be the fewest entities possibility but we'll never know. For what it's worth at EDB I dealt with another case like this and I imagine others have too. I think it's too easy to do things in the wrong order or miss a step and end up with these kinds of problems. I would really like to know what happened here which caused the problem. Do you have records of how you created the slave? When you took the initial image, did you use a cold backup or a hot backup? Did you use pg_start_backup()/pg_stop_backup()? When you failed over was there anything special happening? Was it because of a failure on the master? Was a vacuum full running? When the slave came up do you have the log messages saying it was starting recovery and when it was finishing recovery and starting normal operations? -- greg http://mit.edu/~gsstark/resume.pdf -- 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] ERROR: attempted to delete invisible tuple
On Mon, Aug 17, 2009 at 6:17 PM, Tom Lane wrote: > Hm, what's your current XID counter? (pg_controldata would give an > approximate answer.) I'm wondering if the xmax's are marked committed > but are in the future ... > FWIW that doesn't look right. That would result in HeapTupleBeingUpdated. The only case where it could result in "invisible" is if it's our own xid. Excluding the cases where our own xid is in the tuple I think the relevant cases are either xmin aborted or in progress (or in future) MOVED_OFF and xvac committed MOVED_IN and xvac aborted or is in progress (or in future) -- greg http://mit.edu/~gsstark/resume.pdf -- 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] ERROR: attempted to delete invisible tuple
On Mon, Aug 17, 2009 at 6:17 PM, Tom Lane wrote:
> Bryan Murphy writes:
>> Here's the xmin/xmax/ctid for three problematic records:
>
>> prodpublic=# select xmin,xmax,ctid from items_extended where id in
>> ('34537ed90d7546d78f2c172fc8eed687', '3e1d99b7124742b7aaf2f869f7637b0e',
>> '499b464f141a48619c5ce0475cbe9150');
>> xmin | xmax | ctid
>> ---+---+
>> 262232659 | 308810030 | (689496,3)
>> 262629744 | 308810034 | (692765,1)
>> 262643188 | 308810035 | (692920,9)
Hm, perhaps I should have asked for xvac as well, as that can also
trigger an "invisible" status. You can't select xvac directly but if
you query cmin it might be possible to tell what's going on.
--
greg
http://mit.edu/~gsstark/resume.pdf
--
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] ERROR: attempted to delete invisible tuple
On Mon, Aug 17, 2009 at 4:23 PM, Bryan Murphy wrote: > I've identified 82 bad records. When I try to query for the records, > we get the following: > ERROR: missing chunk number 0 for toast value 25692661 in pg_toast_25497233 > That's fine. I've run into that in a few other tables and have just been > deleting the offending records. However, in this particular table, when I > try and delete the records, I get the following error message: > ERROR: attempted to delete invisible tuple > I'm at a loss what to do next. Can you select xmin,xmax,ctid from the bad tuples? And also the output of pg_resetxlog -n. I suspect you may have failed to restore properly and have tuples from the "future" in your database. Perhaps you're missing at the last WAL file in your recovery? If that's the case you may be able to get out of it with pg_resetxlog and then cleaning up but it would be good to see the data first before changing things. -- greg http://mit.edu/~gsstark/resume.pdf -- 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] Getting database data from the PostgreSQL file system
On Fri, Aug 14, 2009 at 4:31 PM, Ewgenij Sokolovski wrote: > Hello, Guys! Is that kind of thing possible at all? We have a problem that > our database is corrupted, and we are not able to get any table data by > executing SQL requests/running the PG_Admin tool. So, we thought, maybe it is > possible to retrieve data by accessing the data storage directly. The data > folder of PostgreSQL is there, so, theoretically, we should be able to > retrieve all we need, shouldn't we?:) In theory yes, in practice the tool to do it would look a lot like postgres... What corruption symptoms are you actually seeing? -- greg http://mit.edu/~gsstark/resume.pdf -- 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] max_allowed_packet equivalent in Postgres?
On Fri, Aug 14, 2009 at 12:33 AM, Sam Mason wrote: > On Fri, Aug 14, 2009 at 12:03:37AM +0100, Greg Stark wrote: >> On Thu, Aug 13, 2009 at 11:20 PM, Sam Mason wrote: >> > There would be no way of creating a row 1.6TB in size in one go > > I was thinking of a single update per column. Oh, my bad, you did indeed say "row" and I assumed column. Yes, you could create a single row of 1.6TB by doing repeated updates setting one column at a time to a 1G datum. (You would have to be using 32k blocks though) -- greg http://mit.edu/~gsstark/resume.pdf -- 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] max_allowed_packet equivalent in Postgres?
On Thu, Aug 13, 2009 at 11:20 PM, Sam Mason wrote: > On Thu, Aug 13, 2009 at 03:31:39PM -0400, Greg Smith wrote: >> If your client app is coded correctly to handle large packets of data, it >> should work up to the size limits documented at >> http://www.postgresql.org/about/ , so you probably having nothing to worry >> about here. > > Is it worth having a note about having enough memory floating around > for those limits to actually be hit in practice? There would be no > way of creating a row 1.6TB in size in one go, it would be ~800 UPDATE > statements to get it up to that size as far as I can see. That wouldn't work actually. If you did something like "UPDATE tab set a = a || a" the first thing Postgres does when it executes the concatenation operator is retrieve the original a and decompress it (twice in this case). Then it constructs the result entirely in memory before toasting. At the very least one copy of "a" and one copy of the compressed "a" have to fit in memory. To work with objects which don't fit comfortably in memory you really have to use the lo interface. Toast lets you get away with it only for special cases like substr() or length() but not in general. -- greg http://mit.edu/~gsstark/resume.pdf -- 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] comparing NEW and OLD (any good this way?)
On Thu, Aug 13, 2009 at 11:44 PM, Daniel Verite wrote: >> In other discussions about similar issues I've said that the expression: >> >> ROW(NULL,NULL) IS DISTINCT FROM NULL >> >> should evaluate to FALSE. I still think this is correct and generally >> useful behavior. > > I see no reason to disagree with this. Besides, the fact that > ROW(NULL,NULL) IS DISTINCT FROM NULL evaluates to true > while ROW(NULL,NULL) IS NULL also evaluates to true > looks quite puzzling to me. Why is this thread still going on? What does the spec say we should be doing and are we violating it in any of these cases? -- greg http://mit.edu/~gsstark/resume.pdf -- 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] synchronous_commit and mvcc
On Wed, Aug 12, 2009 at 8:28 PM, Kelly Burkhart wrote: > Hello, > > We have synchronous_commit=off in our postgresql.conf file. Does this > setting affect mvcc? If you don't have a crash then there is absolutely no difference from the clients' point of view (besides speed). If you have a crash you could lose the last 200ms of commits but if that happens you'll lose them in a "consistent" way. You can't find the results of one transaction committed afterwards but not some other transaction which came earlier. -- greg http://mit.edu/~gsstark/resume.pdf -- 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] 'a' = any( $${'a','x'} )
On Sat, Aug 8, 2009 at 12:08 PM, Jasen Betts wrote:
> select 'a' = any( $${'a','x'}$$ ) ;
postgres=# select 'a' = any( $${"a","x"}$$ ) ;
?column?
--
t
(1 row)
--
greg
http://mit.edu/~gsstark/resume.pdf
--
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] smart or dumb partition?
On Sat, Aug 8, 2009 at 12:27 AM, Bob Gobeille wrote: > I gather from rtfm that it is typical to set up partitions so that the > "master" table has no records. But from my understanding of partitions and > doing some tests, I don't see any reason that has to be. So I'm wondering > if I'm missing some subtle (or not so subtle) point about partitions? It's purely a convenience issue. Any child partition can be removed later, The parent will be a pain if you ever want to remove it from the partitioning structure. -- greg http://mit.edu/~gsstark/resume.pdf -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Re: character 0xe29986 of encoding "UTF8" has no equivalent in "LATIN2"
On Tue, Aug 4, 2009 at 4:02 PM, Alban Hertroys wrote: > P.S. Please don't top post and keep some context of what you're replying to. > Your messages are a bit confusing the way you write them. These arguments are more convincing if you don't leave the remainder of the OP's message quoted right below them... -- greg http://mit.edu/~gsstark/resume.pdf -- 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] Clients disconnect but query still runs
On Thu, Jul 30, 2009 at 3:20 PM, Tom Lane wrote: > > The earlier part of the discussion was focused on getting the kernel > to actively tell us when the connection had dropped. That would be > workable if we found a way to request it, but I think we'd run out of > options :-( Yeah, everything I've found on SIGIO indicates it would fire whenever the socket was readable which isn't what we want. Does anyone have a pointer for a full documentation on how to use it? -- greg http://mit.edu/~gsstark/resume.pdf -- 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] Clients disconnect but query still runs
On Thu, Jul 30, 2009 at 12:22 PM, Craig Ringer wrote: > > In fact, I'm not even sure _how_ one goes about exiting without sending an > RST. A quick check shows that when I `kill -9' a process with an open client > socket (ssh, in this case) the OS sends a FIN, and responds to the server's > FIN,ACK with its own ACK. So the OS is closing the socket for the dead > process. If I try this with a `psql' process, the server cleans up the > orphaned backend promptly. Fwiw this description sounds like you're leaving out part of the picture. TCP connections consist of two independent flows, one in each direction. Each flow can be closed by a FIN independently. If the client program dies and the client sends a FIN to close the client->server flow that doesn't imply the server will necessarily close the server->client flow. For the situation you described to arise the server would have to notice the EOF on the client connection and explicitly call close. That would happen if the connection was idle or idle-in-transaction. If the server was in the middle of running a query then it won't notice anything until it tries to write to the socket and gets a RST in response because the listening process has died. So there are really two different use cases here. Case 1: the server is idle and reading from a connection and the client has disappeared completely without closing the connection. In that case keepalives or having the server try to send data are the only ways to notice the problem. Case 2: The server is busy and the client has either cleanly closed the connection or died but the server doesn't notice for a long time because it isn't sending any data and it isn't trying to read any data either. -- greg http://mit.edu/~gsstark/resume.pdf -- 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] Clients disconnect but query still runs
On Thu, Jul 30, 2009 at 10:59 AM, Csaba Nagy wrote: > But if I get bad memory or bad wire I'll get much worse problems > already, and don't tell me it will work more reliably if you don't kill > the connection. It's a lot better to find out sooner that you have those > problems and fix them than having spurious errors which you'll get even > if you don't kill the connection in case of such problems. Are you sure? Do you know how many times you haven't even found out you had a problem because TCP just silently kept working despite the problem? Having had to use protocols which imposed their own timeouts on lame hotel networks, buggy wireless drivers, and bad DSL connections and found my connections dying every few minutes I can say it's maddeningly frustrating. Especially knowing that TCP was *supposed* to work in this scenario and they had broken it by trying to be clever. >> Well it ought to have eventually died. Your patience may have ran out >> before the keep-alive timeouts fired though. > > Well it lived for at least one hour (could be more, I don't remember for > sure) keeping vacuum from doing it's job on a heavily updated DB. It was > not so much about my patience as about starting to have abysmal > performance, AFTER we fixed the initial cause of the crash, and without > any warning, except of course I did find out immediately that bloat > happens and found the idle transactions and killed them, but I imagine > the hair-pulling for a less experienced postgres DBA. I would have also > preferred that postgres solves this issue on it's own - the network > stack is clearly not fast enough in resolving it. Indeed, properly set TCP keepalives don't time out for over 2 hours. But that's configurable in postgresql.conf. -- greg http://mit.edu/~gsstark/resume.pdf -- 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] Clients disconnect but query still runs
On Thu, Jul 30, 2009 at 10:27 AM, Csaba Nagy wrote: > > Sorry, I have to disagree here. If there's a spurious network error, you > have usually bigger problems. I prefer to have the connection killed > even if the network recovers I know this is a popular feeling. But you're throwing away decades of work in making TCP reliable. You would change feelings quickly if you ever faced this scenario too. All it takes is some bad memory or a bad wire and you would be turning a performance drain into random connection drops. > than risk an idle in transaction connection > to live forever when the client/network crashes for any reason. In case > of network failure the connection will probably be cleaned eventually, > but it did happen to me that a client machine crashed in the middle of a > transaction while not executing any SQL, and that connection stayed > until I killed it manually. Well it ought to have eventually died. Your patience may have ran out before the keep-alive timeouts fired though. -- greg http://mit.edu/~gsstark/resume.pdf -- 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] Clients disconnect but query still runs
On Thu, Jul 30, 2009 at 7:43 AM, Craig Ringer wrote: > On Wed, 2009-07-29 at 14:56 +0100, Greg Stark wrote: > >> SIGURG might be useful but it would be more complex to use and less >> widely useful since it would only work if the client disconnects >> gracefully (though it might be worth checking into as an alternative >> to our existing query cancel method). > > Might it not also fire if the client disconnects without notice, but tcp > keepalives are enabled? > > I might have to write a little test program and see. > > [much later] My test program did not appear to receive SIGURB, even > after registering for it with fcntl(sockfd, F_SETOWN, ...) and setting a > signal handler for it. This was the case whether the connection was > dropped due to a tcp keepalive failure, the dropping of a network > interface, or a normal disconnect. The next read() or recv() returned > zero bytes read but no asynchronous notification appeared to occur. I'm > under the impression it's really for use with asynchronous sockets, but > haven't tested this yet. Right, you'll only get SIGURG if there's actually any urgent data received. The client would have to actively send such data periodically. That would make this a portability headache since it wouldn't just be an add-on which would fail gracefully if it's unsupported. The server and client would both have to be sure they understood whether they both supported this feature. > > What does work well is occasionally poking the socket with recv(..., > MSG_DONTWAIT) while doing other work. Program attached. TCP keepalives > seem to work very well at least on my Linux test system, and it's easy > to test for a dud connection using recv(...) with the MSG_DONTWAIT and > (if desired) MSG_PEEK flags. If the connection has exited cleanly it'll > return a zero-size read; if the connection has dropped due to keepalive > failure it'll return ETIMEDOUT. The problem with this is that it introduces spurious failures for transient network failures. Also it requires the server to periodically take time out from processing the query to do this. I think we want a zero-cost method which will interrupt processing if the client actively disconnects. If there's a network failure we'll find out about it in the normal course of events. -- greg http://mit.edu/~gsstark/resume.pdf -- 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] Clients disconnect but query still runs
On Thu, Jul 30, 2009 at 8:41 AM, Tatsuo Ishii wrote: >> Well SIGPIPE is no help since it would only fire if we tried to write >> to the socket anyways. > > Right. For this purpose, pgpool sends param packet to client > periodically while waiting for a reply from backend to detect if the > connection to the client is broken. If it's broken, pgool sends cancel > packet to backend not to waste backend machine's CPU cycle. The downside to this is that it will cause spurious failures for transient network failures even if the network comes back before it's actually needed. -- greg http://mit.edu/~gsstark/resume.pdf -- 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 Planner Issues
On Wed, Jul 29, 2009 at 8:01 PM, Jake Stride wrote:
> Hi,
>
> I have 2 databases running on the same server. One is a dump of the
> other, however the query plans for the same query on the same tables
> in each database is wildly different and I cannot work out why.
> -> Seq Scan on
> person_contact_methods e (cost=0.00..3281.89 rows=1 width=8) (actual
> time=114.181..114.181 rows=0 loops=1)
> Filter: (main AND
> ((contact)::text ~~* 'j...@omelett.es'::text) AND (type =
> 'E'::bpchar))
> -> Seq Scan on person_contact_methods e (cost=0.00..2942.06
> rows=1 width=8) (actual time=3.718..184.602 rows=772 loops=1)
> Filter: (("type" = 'E'::bpchar) AND main AND
> ((contact)::text ~~* 'j...@omelett.es'::text))
They don't look like the same data from here. One has no matching
records in this table and the other has over 700.
--
greg
http://mit.edu/~gsstark/resume.pdf
--
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] Clients disconnect but query still runs
On Wed, Jul 29, 2009 at 3:17 PM, Tom Lane wrote: > Greg Stark writes: >> On Wed, Jul 29, 2009 at 1:58 PM, Jasen Betts wrote: >>> can't coerce a signal from the network stack? the linux socket(2) >>> manpage is full of promise (SIGPIPE, SIGURG, SIGIO) > > > And the other question is how much of what you read in the Linux manpage > is portable to any other system... That is a question. But actually I think sigio might be fairly portable -- at least the first hit I found was for someone complaining that it wasn't working on Linux (due to a bug) and this broke their app which worked everywhere else. In any case this would be a feature which if it didn't work would leave us just where we are today. That's another advantage over trying to do something with sigurg which would be far more likely to cause headaches if it behave incorrectly. -- greg http://mit.edu/~gsstark/resume.pdf -- 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] Clients disconnect but query still runs
On Wed, Jul 29, 2009 at 1:58 PM, Jasen Betts wrote: > can't coerce a signal from the network stack? the linux socket(2) > manpage is full of promise (SIGPIPE, SIGURG, SIGIO) [please don't quote the entire message back, just the part you're responding to] Well SIGPIPE is no help since it would only fire if we tried to write to the socket anyways. SIGIO on the other hand looks like exactly what we would need. I'm not sure if it can be set to fire a signal only when the connection is disconnected and not for other state changes but if so it would be interesting. SIGURG might be useful but it would be more complex to use and less widely useful since it would only work if the client disconnects gracefully (though it might be worth checking into as an alternative to our existing query cancel method). -- greg http://mit.edu/~gsstark/resume.pdf -- 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] Video available for PGDay SJC '09
On Wed, Jul 29, 2009 at 12:10 AM, John R Pierce wrote: > Greg Stark wrote: >> >> I think I'm scarred from Quicktime files because they often were >> encoded with codecs like Sorensen which produced proprietary formats. >> >> > > agreed, and the quicktime installer dragging in itunes if you don't remember > to uncheck all the right boxes, and quicktime nagging you to upgrade to > quicktime pro, and the quicktime on windows player having no way to easily > play full screen, and quicktime 'stealing' every video container format on > your system unless you carefully dig through its configurations Well I was talking about the file format, not the player software. -- greg http://mit.edu/~gsstark/resume.pdf -- 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] Video available for PGDay SJC '09
On Tue, Jul 28, 2009 at 3:38 PM, Christophe Pettus wrote: > > Historically, MOV has been the least-bad container format; Flash support on > anything besides Windows has, traditionally, been very spotty. The files > themselves are pretty much the same size; FLV is (as noted) a container > format, not a codec, and the video is H.264 either way. (fwiw H.264 isn't a codec either... it's a compression format which can be generated by various codecs) I think I'm scarred from Quicktime files because they often were encoded with codecs like Sorensen which produced proprietary formats. What does IE or firefox < 3.5 really do if you just link to an mpeg file? Doesn't it run whatever app is set to handle that format? Why is a flash plugin based page better than that? I have a feeling I'm just being iconoclastic for the sake of it here. In reality I would be pretty happy with any page that had a link at the bottom to download an mpeg format file with H.264 data in it that mplayer can play. -- greg http://mit.edu/~gsstark/resume.pdf -- 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] Video available for PGDay SJC '09
On Tue, Jul 28, 2009 at 1:41 PM, Andreas Wenk wrote: > Bill Moran schrieb: > > While I've no objection to someone helping out by converting files, I >> >> find it odd that flv is suggested. I've yet to find anything that can >> play flv files on my FreeBSD desktop machine. I'm pretty sure mplayer >> can play mov files ... I guess I'll find out this evening when I take >> time to watch them. > > You will not need to watch them on your desktop. If your browser supports > flash, then you simply watch them with your browser. I think flash (flv, > swf) is more supported by the browser than mov - as long as you don't > download the videos. For sure mplayer can play them. But why download them > at all? ;-) Why not use a standardized openly documented container format like mpeg4 with mpeg4 part 2 compression instead of one controlled by a single company like flv or quicktime? That would let people play it using any of various open source codecs which can play mpeg4 part 2. -- greg http://mit.edu/~gsstark/resume.pdf -- 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] general question on two-partition table
On Tue, Jul 28, 2009 at 1:08 AM, David Wilson wrote: > On Mon, Jul 27, 2009 at 7:52 PM, Janet Jacobsen wrote: > >> Can you suggest other strategies? > > Something that might be easier to play with is to create a (or > several, to speed up other queries) functional index on the comparison > between rbscore and the cutoff. I think it would be even more interesting to have partial indexes -- ie specified with "WHERE rbscore < cutoff". I'm actually wondering if partitioning is really what you want. You might prefer to just keep two entirely separate tables. One that has all the data and one that has a second copy of the desirable subset. Kind of like a "materialized view" of a simple query with the where clause of "rbscore < cutoff". -- greg http://mit.edu/~gsstark/resume.pdf -- 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] Disable databse listing for non-superuser (\l) ?
On Sat, Jul 25, 2009 at 2:53 PM, Andreas Wenk wrote: >> I mean, didn't Apple just kill someone for letting their new iPhone >> design leak? > > this is now going off topic - but what do you mean with your last sentence? Please don't quote an entire message if you're only responding to part of it. (That's what people are really talking about when they say top-posting is bad.) see: http://www.theregister.co.uk/2009/07/22/security_offical_suspended/ I don't think there's any actual indication that Apple was involved directly. -- greg http://mit.edu/~gsstark/resume.pdf -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Re: user/grant - best practices handling permission in production system
On Thu, Jul 23, 2009 at 10:09 PM, Stefano Nichele wrote: > 2. using the user used in step 1, create the schema and populate tables with > At this point the webapp should work correctly. > The main missing point for me is how to perform step 4 in a simple way since > it seems there is not a way to give the right grants to all db objects in > one shot. Well there isn't a way to do step 2 in one shot either. You'll have to issue a CREATE statement for each object, it's no extra work to issue a GRANT for each object with the specific rights the application should have at that time. Think of it as an important part of the process of creating a new object. Note that it's probably not necessary to grant all rights to every table. Most applications have some tables that are read-only or insert-only from the point of view of the application. Your system will be more secure if the application does not have unnecessary privileges. So thinking about what rights to grant to the application for each object when it's created is not a bad thing. -- greg http://mit.edu/~gsstark/resume.pdf -- 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] 3des key lengths and key management
On Thu, Jul 23, 2009 at 6:11 PM, bulk wrote: > 1) What are the default 3des key lengths when you load postgresql > enterprise db on a redhat ES x86_64 box? Traditionally 3des can use either 112-bit or 56-bit keys. I think the openssl interface actually lets you set the third key separately now but I'm not sure that adds any security. > 2) If possible how can you change the keys? and replace them with keys > with lengths to 2048 bit or above? > 3) If 2 is not possible then what other encryption type can we use that > will meet his 2048 bit key length requirement? He's completely confused. Symmetric key encryption like DES/3DES/AES use key sizes much smaller than this. 3DES is still considered reasonably secure with 112 bit keys, though the real state of the art today is AES which uses key sizes of 128, 192, or 256. He or she is thinking of asymmetric encryption like RSA. For those algorithms key sizes are often in the range 1024, 2048, or even higher -- 2048 isn't actually considered very high any more. But those keys are generally used differently. They're used to sign or protect hashes or keys which are then generated or used with symmetric encryption algorithms. They're far too slow to use for the actual encryption itself. > 4) Is is possible to compile C or Java code that will allow me to be the > only one whom knows the pass-key but allow other users to encrypt/decrypt > data? Well you need the pass-key to do the encryption or decryption. With asymmetric algorithms you can separate those two functions so the group which can encrypt is separate from the group which can decrypt. You can create a function to do the encryption and another function to do decryption, make it SECURITY DEFINER, and grant access to only certain users. But then anyone who has a dump of your database will have the key which kind of defeats the purpose of using encryption. -- greg http://mit.edu/~gsstark/resume.pdf -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] problem with pg_restore?
On Thu, Jul 23, 2009 at 3:14 PM, Sam Mason wrote:
> On Thu, Jul 23, 2009 at 07:40:18AM -0600, Scott Marlowe wrote:
>> On Tue, Jul 14, 2009 at 5:47 PM, Jim Michaels wrote:
>> > could somebody rewrite pg_dumpall and pg_dump so that it makes editable
>> > dumps?
>> > most programmer's text editors can't handle more than 2000
>> > characters per line. and I want to be able to edit my dumps.
You need to get yourself a better editor.
>> When I need to make changes to large dumps I use tools like sed, awk
>> and diff, not a text editor.
>
> Indeed, but I still like to be able to use a text editor to verify that
> my code is doing the right thing. Obviously for large files (i.e. a GB
> and over) it's not going to work, but I'd still expect tools to work
> ("less -n" seems to be my tool of choice at the moment).
Actually traditionally tools like sed, awk, etc had fixed-size line
buffers. If your system has a BSD-derived set of tools you may or may
not run into problems depending on whether they've been reimplemented
since. One of the main distinguishing features of the GNU tools was
specifically that they had a policy of choosing implementations that
removed arbitrary limits even if it meant less efficient
implementations.
--
greg
http://mit.edu/~gsstark/resume.pdf
--
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] Can LIKE under utf8 use INDEXes?
On Wed, Jul 22, 2009 at 5:57 PM, Robert James wrote: > Hi. I'm confused about the behavior of LIKE under utf8 locale. > Accoding to the docs ( > http://www.postgresql.org/docs/8.2/interactive/locale.html - excerpted > below), it seems that LIKE ignores locale and hence can't use indexes. Yet, > EXPLAIN clearly shows it using indexes. Are you sure you're using 8.2? -- greg http://mit.edu/~gsstark/resume.pdf -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
