[GENERAL] scope quirk in copy in function
create or replace function archive_some_stuff() returns void as $$ declare cutoff timestamptz; begin cutoff := now() - '1 day'::interval; copy (select * from log where end_when < cutoff) to ... ... Gives me an error that there is column named cutoff. (Other uses of cutoff in queries not inside a copy, iow the delete from commands, work.) Is there any alternative to just duplicating the now() expression inside every copy? -- Scott Ribe scott_r...@elevated-dev.com http://www.elevated-dev.com/ https://www.linkedin.com/in/scottribe/ (303) 722-0567 voice -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] quick q re execute & scope of new
Easier to give an example than describe the question, any chance of making
something like this work?
execute('insert into ' || tblname || ' values(new.*)');
--
Scott Ribe
scott_r...@elevated-dev.com
http://www.elevated-dev.com/
https://www.linkedin.com/in/scottribe/
(303) 722-0567 voice
--
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] quick q re execute & scope of new
On Apr 2, 2015, at 10:10 PM, Tom Lane wrote:
>
> Not like that, for certain. It might work to use EXECUTE ... USING new.*
> or some variant of that.
Couldn't get a variant of that to work, but this did:
execute('insert into ' || tblnm || ' select $1.*') using new;
--
Scott Ribe
scott_r...@elevated-dev.com
http://www.elevated-dev.com/
https://www.linkedin.com/in/scottribe/
(303) 722-0567 voice
--
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] quick q re execute & scope of new
On Apr 2, 2015, at 10:14 PM, Adrian Klaver wrote:
>
> EXECUTE 'insert into ' || quote_ident(tblname) || ' values(' || new.* || ')'
Not that easy, strings are not quoted correctly, and null values are blank.
Might be a function to translate new.* into a string as needed for this use,
but I found another way based on Tom's suggestion:
execute('insert into ' || tblnm || ' select $1.*') using new;
--
Scott Ribe
scott_r...@elevated-dev.com
http://www.elevated-dev.com/
https://www.linkedin.com/in/scottribe/
(303) 722-0567 voice
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] recover from this error
Alright, check kernel version, but what else, dump & restore? ERROR: unexpected data beyond EOF in block 1 of relation base/16388/35954 HINT: This has been seen to occur with buggy kernels; consider updating your system. -- Scott Ribe scott_r...@elevated-dev.com http://www.elevated-dev.com/ https://www.linkedin.com/in/scottribe/ (303) 722-0567 voice -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] different empty array syntax requirements
How is that one pgsql build (both are 9.5.2) has different casting behavior for
empty arrays:
ericmj=# select ARRAY[]::text[];
array
---
{}
(1 row)
ericmj=# select (ARRAY[])::text[];
array
---
{}
(1 row)
--VS--
pedcard=# select ARRAY[]::text[];
array
---
{}
(1 row)
pedcard=# select (ARRAY[])::text[];
ERROR: cannot determine type of empty array
LINE 1: select (ARRAY[])::text[];
^
HINT: Explicitly cast to the desired type, for example ARRAY[]::integer[].
--
Scott Ribe
scott_r...@elevated-dev.com
http://www.elevated-dev.com/
https://www.linkedin.com/in/scottribe/
(303) 722-0567 voice
--
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] different empty array syntax requirements
On Apr 21, 2016, at 8:25 PM, Alvaro Aguayo Garcia-Rada wrote: > > Looks like one has the appropiate cast operator, while the other hasn't. Have > you tried doing the same, on both server, on an empty database created from > template0? Excellent suggestion: pedcard=# create database test; CREATE DATABASE pedcard=# \c test SSL connection (protocol: TLSv1, cipher: DHE-RSA-AES256-SHA, bits: 256, compression: off) You are now connected to database "test" as user "admin". test=# select (ARRAY[])::text[]; ERROR: cannot determine type of empty array LINE 1: select (ARRAY[])::text[]; ^ HINT: Explicitly cast to the desired type, for example ARRAY[]::integer[]. -- Scott Ribe scott_r...@elevated-dev.com http://www.elevated-dev.com/ https://www.linkedin.com/in/scottribe/ (303) 722-0567 voice -- 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] different empty array syntax requirements
On Apr 21, 2016, at 8:37 PM, Tom Lane wrote: > > The parens turn into an actual parsetree node when > operator_precedence_warning is on, and the cast-of-an-array hack doesn't > know it should look through such a node. I figured that. The mystery is why on my pg, and not on the other. I've asked the other guy to try it in a newly-created database. > That's a bug. Will fix it. OK, cool. -- Scott Ribe scott_r...@elevated-dev.com http://www.elevated-dev.com/ https://www.linkedin.com/in/scottribe/ (303) 722-0567 voice -- 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] Uhm, so, yeah, speaking of /.
> I thought it had to do with letting a form sit around too long and > then /. timing out the state. > > That's probably not good anyway: it should at least give you a real > error message. However, they might not consider that a bug. I didn't let the form sit around at all--didn't think to mention that before. It may well not be related to MySQL at all, the point is simply that although /. is well-known, gets a lot of hits, and works well enough for its intended purpose, it is buggy and is NOT an example of what would be acceptable reliability for most "mission critical" applications. -- Scott Ribe [EMAIL PROTECTED] http://www.killerbytes.com/ (303) 722-0567 voice ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Slightly OT.
>>> ...fully transparent replication... >> >> There is no such thing. Asking for it implies ignorance of the issues >> involved and what is actually available with other database products. >> > > We are darn close ;) Argh, to be clear: I was referring to multimaster. -- Scott Ribe [EMAIL PROTECTED] http://www.killerbytes.com/ (303) 722-0567 voice ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] why postgresql over other RDBMS
> So it works right now, except it doesn't have (yet) the infrastructure to > keep the scans synchronized Perhaps you only got one read of the table because the process is essentially self-synchronizing. Whenever one process "gets ahead", it requires a disk read for the next page, which causes it to block for a relatively long time, during which time the other two processes either proceed reading rows from cache, or come to the end of the cache and block waiting for the same page to be read from disk. Obviously not a guarantee, as indexing a relatively more expensive type COULD cause one process to get multiple pages behind, and memory usage by other processes COULD cause intervening pages to be flushed from cache. But I have a suspicion that the experiment was not just a happy fluke, that there will be a strong tendency for multiple simultaneous index operations to stay sufficiently closely synch'd that the table will only be read from disk once. (Especially when such operations are done while the database is otherwise quiescent, as would be the typical case during a restore.) -- Scott Ribe [EMAIL PROTECTED] http://www.killerbytes.com/ (303) 722-0567 voice ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Continuous PITR (was Re: multimaster)
> P.S. it's not the "the cloud" anymore, it's "the tubes". It was always tubes. The cloud was just a convenient simplification for the technically declined ;-) -- Scott Ribe [EMAIL PROTECTED] http://www.killerbytes.com/ (303) 722-0567 voice ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Corruption of files in PostgreSQL
I don't use lo_import and lo_export myself, but is there any way to log their usage? It certainly sounds as though step 1 for this user is to keep track of how much data is handed to PG for each file, and how much data is returned to PG for each file (and how much data is in the file at the time of the request). -- Scott Ribe [EMAIL PROTECTED] http://www.killerbytes.com/ (303) 722-0567 voice ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Update a single row without firing its triggers?
Why not just write the trigger function as: if old.a is distinct from new.a or old.b is distinct from new.b ... end if -- Scott Ribe [EMAIL PROTECTED] http://www.killerbytes.com/ (303) 722-0567 voice ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Take your postgresSql on the road, and live to tell of it.
You can also have a trigger that records into a log table the id & table of each record inserted/updated/deleted, and then it's a simple matter of merging changes from a certain point forward by searching that table and using the values of the current records. -- Scott Ribe [EMAIL PROTECTED] http://www.killerbytes.com/ (303) 722-0567 voice ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] an other provokative question??
> Relational database pioneer says technology is obsolete The headline is grossly misleading; the article is only somewhat less misleading. The actual blog entry: - Does not claim that relational databases per se are obsolete; - Claims that the idea of a "single one size fits all" implementation of the physical storage strategy for a relational db is obsolete; - Does not claim that column-oriented storage is better for all use cases; - Does claim that column-oriented is vastly superior for data warehouses; - Further claims that for all use cases there exists some specialized storage strategy that will perform better than the standard row-oriented strategy. -- Scott Ribe [EMAIL PROTECTED] http://www.killerbytes.com/ (303) 722-0567 voice ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Database reverse engineering
Embarcadero's tools are quite nice, quite pricey, Windows only. -- Scott Ribe [EMAIL PROTECTED] http://www.killerbytes.com/ (303) 722-0567 voice ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] NOT NULL Issue
Another way is to remember that NULL is a distinguished thing that is absence of a value, not any value of any type, and this applies to all types: - the integer 0 is a value, not null - the date 1/1/1900 (or 1904 or ) is a value, not null - the time 00:00:00 is a value, not null - and the string '' is a value, not null -- Scott Ribe [EMAIL PROTECTED] http://www.killerbytes.com/ (303) 722-0567 voice ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
[GENERAL] Building Windows fat clients
I'm asking this group because we tend to think alike wrt to data modeling and separation of concerns ;-) Any recommendations on ORM libraries for new Windows development? The last time I started anything from scratch was over 10 years ago, and the "state of the art" seemed to be to smash everything together into event handlers on GUI objects. Ugh. I pulled the M of the MVC out into separate coherent classes and implemented a *very* simple ORM, leaving the VC mostly conflated in the event handlers--which is not too bad since this app will never need to be cross-platform. So the dev tool was discontinued, some closed-source libraries are getting less and less compatible by the year, and we're going to rewrite. Where to start? It's a custom Windows-only app, only installed at one site. Using .NET would be fine. C# or C++ would be most-preferred language choices, although we could suck it up and use Java. I don't want to put VB on the table. Leaning toward Visual Studio .NET because I know it will be around (in whatever morphed form) for a while; but also considering Borland's supposedly revitalized C++ tools because I used C++ Builder with success back when MS C++ compilers were still awful. I should probably mention that the Windows apps, with the exception of one complicated "explore customer's entire history here" screen, are pretty simple; the complexity is in reports and stored procedures. Suggestions where to start? -- Scott Ribe [EMAIL PROTECTED] http://www.killerbytes.com/ (303) 722-0567 voice ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] RETURN NEXT on result set
Feature request: allow some way to "return next" a set of values. Usage: recursive stored procedures to walk a tree. Example: given a table, find all tables that inherit from it. Right now, as far as can tell, that requires a little extra effort to merge the results from different levels of recursion: create or replace function "DbGetDescendantTables" (oid) returns setof oid as $$ declare parentid alias for $1; curid1 oid; curid2 oid; c1 refcursor; c2 refcursor; begin return next parentid; open c1 for select inhrelid from pg_inherits where inhparent = parentid; while 1 loop fetch c1 into curid1; if found then open c2 for select * from "DbGetDescendantTables"(curid1); while 1 loop fetch c2 into curid2; if found then return next curid2; else exit; end if; end loop; close c2; else exit; end if; end loop; close c1; end; $$ language 'plpgsql'; But if a query result could directly be added to the result set being accumulated, this would become: create or replace function "DbGetDescendantTables" (oid) returns setof oid as $$ declare parentid alias for $1; curid1 oid; c1 refcursor; begin return next parentid; open c1 for select inhrelid from pg_inherits where inhparent = parentid; while 1 loop fetch c1 into curid1; if found then return next (select * from "DbGetDescendantTables"(curid1)); else exit; end if; end loop; close c1; end; $$ language 'plpgsql'; Sure, some of this could be avoid by accumulating and returning an array, but in my case it's convenient for the procedures to produce result sets. -- Scott Ribe [EMAIL PROTECTED] http://www.killerbytes.com/ (303) 722-0567 voice ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] RETURN NEXT on result set
> There is a new RETURN QUERY in 8.3 that may be what you want. Sounds good. -- Scott Ribe [EMAIL PROTECTED] http://www.killerbytes.com/ (303) 722-0567 voice ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] Triggers & inheritance
Triggers have never been inherited, right? Not in any version? I'm pretty sure that's the case, but I'm debugging some old logging and just need to confirm it. -- Scott Ribe [EMAIL PROTECTED] http://www.killerbytes.com/ (303) 722-0567 voice ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Inheritance fixing timeline? (Was "Inherited FK Indexing")
> Is this set to be fixed in any particular release? Depending on what you're doing, this may be overkill, but: I have child tables that not only need FK constraints, but also triggers and the functions called by the triggers. So instead of writing this over and over again, I eventually wrote a single procedure that takes the name of the table, and using dynamic sql (execute command), generates the FKs and the procedures and the triggers. You *could* take it a step further, and have a procedure which takes the name of the base table, finds all inherited tables, and makes sure everything is set up correctly. I haven't, and probably won't, because I'm a solo developer and don't make additions to the schema at such a great rate that I would have trouble remembering to run my current "FooChild_Setup" function on a new table. -- Scott Ribe [EMAIL PROTECTED] http://www.killerbytes.com/ (303) 722-0567 voice ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] good sql tutorial
I think it's out of print, unfortunately, but by far the best quick intro I've ever seen is: "The Essence of SQL: A Guide to Learning Most of SQL in the Least Amount of Time" by David Rozenshtein. -- Scott Ribe [EMAIL PROTECTED] http://www.killerbytes.com/ (303) 722-0567 voice ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] can I define own variables?
> can I define connection-global variables within a ODBC connection ? Temp table, containing only 1 row, one column per variable. If you so wish, wrap it up in stored procedures for creating, setting, and accessing. -- Scott Ribe [EMAIL PROTECTED] http://www.killerbytes.com/ (303) 722-0567 voice ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Inheritance foreign key unexpected behaviour
PostgreSQL foreign keys won't enforce restrictions the way you want them to; you'll have to use a trigger. And at that point, you might as well consider alternative designs... -- Scott Ribe [EMAIL PROTECTED] http://www.killerbytes.com/ (303) 722-0567 voice ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] running postgresql
Well it defaults to mapping to the current user, so you would have wanted: psql -U myuser mydb Or just create a postgres user named dagon and create the db as owned by that user. Or su myuser before running psql... -- Scott Ribe [EMAIL PROTECTED] http://www.killerbytes.com/ (303) 722-0567 voice ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Linux v.s. Mac OS-X Performance
> my understanding was that the lack of threading on OSX made it > especially poor for a DB server What you're referring to must be that the kernel was essentially single-threaded, with a single "kernel-funnel" lock. (Because the OS certainly supported threads, and it was certainly possible to write highly-threaded applications, and I don't know of any performance problems with threaded applications.) This has been getting progressively better, with each release adding more in-kernel concurrency. Which means that 10.5 probably obsoletes all prior postgres benchmarks on OS X. -- Scott Ribe [EMAIL PROTECTED] http://www.killerbytes.com/ (303) 722-0567 voice ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Query Performance Test
> So, how can I do to execute it as if it was the first > time again? Reboot. As Lew pointed out, that might not actually be a good idea, because caching means that most queries will most of the time not run with that "first time" performance. -- Scott Ribe [EMAIL PROTECTED] http://www.killerbytes.com/ (303) 722-0567 voice ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Primary Key
> It's worse than that. It's even worse than that. Decades ago, Florida used to issue multiple plates with the same number, differentiated by color. There are other cases of states having multiple types of license plates, with overlapping numbers. -- Scott Ribe [EMAIL PROTECTED] http://www.killerbytes.com/ (303) 722-0567 voice ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Linux v.s. Mac OS-X Performance
> In general, you can expect any Unix based OS, which includes MacOS X, to > perform noticeably better than Windows for PostgreSQL. Is that really true of BSD UNIXen??? I've certainly heard it's true of Linux. But with BSD you have the "kernel funnel" which can severely limit multitasking, regardless of whether threads or processes were used. Apple has been working toward finer-grained locking precisely because that was a serious bottleneck which limited OS X server performance. Or have I misunderstood and this was only the design of one particular flavor of BSD, not BSDen in general? -- Scott Ribe [EMAIL PROTECTED] http://www.killerbytes.com/ (303) 722-0567 voice ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Linux v.s. Mac OS-X Performance
> Kind of. Mach is still running underneath (and a lot of the app APIs > use it directly) but there is a BSD 'personality' above it which > (AIUI) is big parts of FreeBSD ported to run on Mach. Right. Also, to be clear, OS X is not a true microkernel architecture. They took the "division of responsibilities" from the Mach microkernel design, but Mach is compiled into the kernel and is not a separate process from the kernel. -- Scott Ribe [EMAIL PROTECTED] http://www.killerbytes.com/ (303) 722-0567 voice ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Linux v.s. Mac OS-X Performance
> There are claims this > is improved in current systems (Leopard + Intel), but the margin was so > big before... IIRC, it was later established that during those tests they had fsync enabled on OS X and disabled on Linux. -- Scott Ribe [EMAIL PROTECTED] http://www.killerbytes.com/ (303) 722-0567 voice ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Linux v.s. Mac OS-X Performance
> Yes, very much so. Windows lacks the fork() concept, which is what makes > PostgreSQL much slower there. So grossly slower process creation would kill postgres connection times. But what about the cases where persistent connections are used? Is it the case also that Windows has a performance bottleneck for interprocess communication? -- Scott Ribe [EMAIL PROTECTED] http://www.killerbytes.com/ (303) 722-0567 voice ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] PostgreSQL 7.4.16 is creating strange files under /var/lib/pgsql
On Oct 7, 2010, at 9:02 PM, Craig Ringer wrote: > Where? They can't be in /var/lib/pgsql if there are four of them, so they > must be in subdirectories, right? Which ones? Or they're created with odd characters which the user's shell/terminal/UI cannot display. -- Scott Ribe scott_r...@elevated-dev.com http://www.elevated-dev.com/ (303) 722-0567 voice -- 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] What was new in 8.4 & 8.3?
On Oct 11, 2010, at 4:35 PM, David Fetter wrote: > Should we have a 9.1 one? There's already been at least one large, > new feature, namely INSTEAD OF triggers. I think so. From my point of view, not needed until the release, or at least the feature set is frozen for sure. For people with a high enough level of interest to track things earlier, there are already sources. I'm thinking of a page to support those who do not follow dev, and periodically decide they now have time to start using new features... -- Scott Ribe scott_r...@elevated-dev.com http://www.elevated-dev.com/ (303) 722-0567 voice -- 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] It is possible to update more than 1 table in the same update statement?
On Nov 7, 2010, at 7:58 AM, Andre Lopes wrote: > need to update various tables in the same update. It is possible to do it? Transactions??? -- Scott Ribe scott_r...@elevated-dev.com http://www.elevated-dev.com/ (303) 722-0567 voice -- 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] It is possible to update more than 1 table in the same update statement?
On Nov 7, 2010, at 8:20 AM, Andre Lopes wrote: > I mean update more than one table at the same time with something like this: Why? Use a transaction. -- Scott Ribe scott_r...@elevated-dev.com http://www.elevated-dev.com/ (303) 722-0567 voice -- 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] It is possible to update more than 1 table in the same update statement?
On Nov 7, 2010, at 8:37 AM, Andre Lopes wrote: > The only way I can guarantee a transaction is in a Function or there are > other ways? <http://www.postgresql.org/docs/9.0/interactive/tutorial-transactions.html> -- Scott Ribe scott_r...@elevated-dev.com http://www.elevated-dev.com/ (303) 722-0567 voice -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Why facebook used mysql ?
On Nov 9, 2010, at 7:04 AM, Allan Kamau wrote: > have come up with a few > (possibly wrong) theories. They all sound reasonable. I think you missed an important one though: aggressive (and even sometimes outright false) promotion and sales by the company MySQL AB. Why I started looking at databases, you didn't have to look very hard to find PostgreSQL, but you did have to at least make a minimal effort. Also, my understanding is that if you go way back on the PostgreSQL timeline to versions 6 and earliest 7.x, it was a little shaky. (I started with 7.3 or 7.4, and it has been rock solid.) -- Scott Ribe scott_r...@elevated-dev.com http://www.elevated-dev.com/ (303) 722-0567 voice -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] streaming replication feature request
How about supporting something like: wal_keep_segments = '7d' -- Scott Ribe scott_r...@elevated-dev.com http://www.elevated-dev.com/ (303) 722-0567 voice -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] identifying local connections
Assume I have a local process which leaves a transaction open & idle for an extended period of time. Is there any way to identify the local process connected to a particular backend? pg_stat_activity is fine for TCP connections but does not provide useful identifying information for domain socket connections. I just upgraded to 9, and will implement set application_name in my various server daemons, but was wondering if there's a way to identify this process right now. -- Scott Ribe scott_r...@elevated-dev.com http://www.elevated-dev.com/ (303) 722-0567 voice -- 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] identifying local connections
On Nov 15, 2010, at 8:50 AM, Tom Lane wrote: > netstat will probably work for this, depending on what platform you're on. OS X. I can see the connections, but I don't see an option to display PIDs. -- Scott Ribe scott_r...@elevated-dev.com http://www.elevated-dev.com/ (303) 722-0567 voice -- 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] identifying local connections
On Nov 15, 2010, at 9:24 AM, Tom Lane wrote: > In that case see lsof --- you can match up the ends of the connection > using the hex value in the "device" column. > > regards, tom lane > Yes, that works. Match "Address" from netstat to "DEVICE" in lsof. Thanks. -- Scott Ribe scott_r...@elevated-dev.com http://www.elevated-dev.com/ (303) 722-0567 voice -- 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] identifying local connections
On Nov 15, 2010, at 9:05 AM, Steve Clark wrote: > netstat -an will do it on linux. > > sockstat will do it on FreeBSD. > > What's OS X ? ;-) Apple must use very different option switches for their netstat, because I see no way to get PIDs (which seems like a pretty big oversight to me), and -an would not make sense: -a Include directory entries whose names begin with a dot (.). -n Display user and group IDs numerically, rather than converting to a user or group name in a long (-l) output. This option turns on the -l option. ;-) -- Scott Ribe scott_r...@elevated-dev.com http://www.elevated-dev.com/ (303) 722-0567 voice -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] figuring out a streaming replication failure
The standby log: -> 2010-11-14 17:40:16 MST - 887 -LOG: database system was shut down in recovery at 2010-11-14 17:40:10 MST -> 2010-11-14 17:40:16 MST - 887 -LOG: entering standby mode -> 2010-11-14 17:40:16 MST - 887 -LOG: consistent recovery state reached at 3/3988FF8 -> 2010-11-14 17:40:16 MST - 887 -LOG: redo starts at 3/3988F68 -> 2010-11-14 17:40:16 MST - 887 -LOG: invalid record length at 3/3988FF8 -> 2010-11-14 17:40:16 MST - 885 -LOG: database system is ready to accept read only connections -> 2010-11-14 17:40:16 MST - 890 -LOG: streaming replication successfully connected to primary -> 2010-11-15 02:24:26 MST - 890 -FATAL: could not receive data from WAL stream: FATAL: requested WAL segment 000100030004 has already been removed -> 2010-11-15 02:24:26 MST - 887 -LOG: unexpected pageaddr 2/B9BF2000 in log file 3, segment 4, offset 12525568 -> 2010-11-15 02:24:27 MST - 2790 -LOG: streaming replication successfully connected to primary -> 2010-11-15 02:24:27 MST - 2790 -FATAL: could not receive data from WAL stream: FATAL: requested WAL segment 000100030004 has already been removed -> 2010-11-15 02:24:32 MST - 2791 -LOG: streaming replication successfully connected to primary -> 2010-11-15 02:24:32 MST - 2791 -FATAL: could not receive data from WAL stream: FATAL: requested WAL segment 000100030004 has already been removed ... Now, the standby is geographically isolated from the master, so it's over an internet connection, so it's not a shock that with a large enough update and wal_keep_segments not large enough, speed of disk would outrun speed of network sufficiently for this to happen. But as far as I know there was almost no write activity at 2am, no active users at all, no batch processing. There is a pg_dumpall that kicks off at 2am and these errors start about the same time that it finished. I also did the original synch and standby launch immediately after a mass update before autovacuum had a chance to run, so at some point there would be a lot of tuples marked dead. wal_keep_segments was at 64, the first segment still around was 000100030010, checkpoint_segments was 16. In the midst of the pg_dumpall the master logs do show messages about checkpoint flushing too often. The 70ish log segments still around show mod times right around 2:23, progressing a second or so each, whereas they were created over a much longer period going back to the day before. 1 question: what happened here? Why were log files created the day before updated? 1 suggestion: would it be possible to not delete wal segments that are needed by a currently attached standby? -- Scott Ribe scott_r...@elevated-dev.com http://www.elevated-dev.com/ (303) 722-0567 voice -- 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] Survey on backing up unlogged tables: help us with PostgreSQL development!
On Nov 16, 2010, at 3:46 PM, Josh Berkus wrote: > ...and will be truncated (emptied) on database restart. I think that's key. Anything that won't survive a database restart, I sure don't expect to survive backup & restore. -- Scott Ribe scott_r...@elevated-dev.com http://www.elevated-dev.com/ (303) 722-0567 voice -- 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] Whose bug is this?
On Nov 18, 2010, at 8:22 AM, Tom Lane wrote: > AFAICT, the way this works on OS X is that > > (1) At the time of shared library build, you specify its eventual > install location with "-install_name /full/path/and/filename". > > (2) When an executable is linked against such a library, the > install_name is recorded in the executable (whether the library was > found in that location or not --- this supports linking in a build > tree). > > (3) At runtime, the library had better be in the declared location. > > So what the symptoms sound like is that you're trying to use libraries > that were built to be in someplace other than where they are. > "otool" might help you in figuring out their intended location. > > You say that the recorded install_name is just the library name without > path --- how sure are you of that? Because it appears to me that the > Postgres makefiles will always specify a full path when building a shlib > on Darwin. Somebody would have had to hack up src/Makefile.shlib, or > munge the libraries after the fact, to get a path-less install_name. Basically correct. I believe it is possible to link just a file name without a path so that standard locations will searched. It is also possible to use some notations or other like @executable/... for paths relative to an app's actual location (to support embedding the libraries in an app bundle and avoid having to install them separately). Possibilities: - Forget whatever package you used. Download the source, configure & make & install the usual way to get a normal installation. This has the advantage that it will give you an installation that everybody here understands. It has the disadvantage that it will only build libraries for your particular platform, PPC or x86, 32- or 64-bit. Then you have to go through some gyrations to get fat binaries. (Of course, we don't know whether or not you currently have a fat build anyway.) - Whether you use the current package or build from source yourself, copy the static versions of the libraries in to your project somewhere, and link them in. Then you have no worries about paths for dynamic linking. (You must copy them, because the linker has a strong preference for dynamic libraries. If you try to link to a static library, and it finds the dynamic library in the path, it will use that instead. So you have to get the static libs into a path where the dynamic libs won't be found.) - Copy the dynamic libraries into your app bundle, and use name_tool (IIRC) to change the install paths to ones relative to the executable. -- Scott Ribe scott_r...@elevated-dev.com http://www.elevated-dev.com/ (303) 722-0567 voice -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] limits of constraint exclusion
Consider the following test setup: create table t1 ( id int8 primary key, name varchar not null unique ); create table t2 ( id int8 primary key, t1_id int8 not null references t1 ); create table t2a ( primary key(id), check(t1_id = 1) ) inherits (t2); create table t2b ( primary key(id), check(t1_id = 2) ) inherits (t2); insert into t1 values(1, 'foo'); insert into t1 values(2, 'bar'); Now a simple query shows constraint exclusion; the following shows only t2 and t2a being checked: explain select * from t1, t2 where t1.id = t2.t1_id and t1.id = 1; But the following shows t2, t2a, and t2b being checked: explain select * from t1, t2 where t1.id = t2.t1_id and t1.name = 'foo'; And I tried to make the "it only involves a single t1 and matches a single partition" more explicit, but this didn't do it either: explain with tbl as (select id from t1 where name = 'foo') select * from t1, t2 where t1.id = t2.t1_id and t1.id = (select id from tbl); Granted these are near-empty tables, but I'm seeing the same behavior with real data and a real (complicated, 6-way join) query, where the vast majority of time is spent scanning the indexes of tables that cannot possibly contain any matching values. In that case, there's currently 55,000,000 rows spread over 87 partitions (the row count will grow steadily, the partition count will remain mostly the same). It's like this one, in that the constraint column is an integer and the check constraint is simple equality, not a range or list. And there is no index on the constraint column, since for every partition there is only a single value in that column--which means the planner winds up using a different index to scan the partitions (and it is a highly-selective index, so if it's going to scan non-matching partitions, it's not a bad index to use). I do have a workaround, in that there's only 1 special case where the performance matters, and in that case it's easy to directly join with the single appropriate partition. But I do wonder if I'm missing some way to encourage the planner to exclude partitions, or if this is forming into some sort of feature request, where potential exclusive constraints are passed through, so that before performing the index scan the executor can decide to skip the scan and return no matches? One additional wrinkle is that though I'm mostly concerned about a single query that hits a single partition, I also have a view, and queries against that could hit any partition (usually only one, but sometimes multiples). -- Scott Ribe scott_r...@elevated-dev.com http://www.elevated-dev.com/ (303) 722-0567 voice -- 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] alter table add column - specify where the column will go?
On Nov 24, 2010, at 9:42 AM, Derrick Rice wrote: > > Even if an example doesn't exist, you can at least imagine a scenario where > an improvement to postgresql is made such that the column order is decided > internally, rather than by table definition. Not when SQL compatibility requires that the order be maintained. -- Scott Ribe scott_r...@elevated-dev.com http://www.elevated-dev.com/ (303) 722-0567 voice -- 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] Looking for auto starting procedures
On Dec 2, 2010, at 11:32 AM, wrote: > > The usual notification from postgreSQL does not allow to write > an [own, better to evaluate] identifier in the syslog. This is > not a matter of the syslog daemon, its on the program which logs. > May be, I have just not found this. Am I missing something you require, or would this do it: raise notice 'mynotice: %', some_id; > An auto running stored procedure would solve the problem, if therewould be > a way to run this procedure on server startup automatically > [as it looks, write another daemon for this]. I've actually wanted that as well ;-) But it's not that hard to arrange for your script that starts the PG server to also run some SQL after the server launch. -- Scott Ribe scott_r...@elevated-dev.com http://www.elevated-dev.com/ (303) 722-0567 voice -- 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] Looking for auto starting procedures
On Dec 2, 2010, at 1:27 PM, wrote:
>
> I have not understand, where I can issue direct sql statements
> and it looks like, the RAISE is not possible with plSql:
Right, it's not actually SQL, so you can't use it in plain SQL. It is part of
the plpgsql procedural language.
So you could easily create a small stored procedure, for example:
create function myraise(msg varchar, id varchar) returns void as $$ begin
raise notice '%: %', msg, id;
end; $$ language plpgsql;
and call that from SQL:
select myraise ('mymsg', '1234');
--
Scott Ribe
scott_r...@elevated-dev.com
http://www.elevated-dev.com/
(303) 722-0567 voice
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: *****SPAM***** Re: [GENERAL] if-clause to an exiting statement
On Dec 7, 2010, at 4:58 AM, kobi.biton wrote: > > hi thanks for the reply I did look at the CASE statement however cannot seem > to alter the returned row-count ... Well, yeah. The row count is the count of rows returned. If there are no rows matched by the query, then what exactly do you expect to happen? Set the row count to 1, so that the application then tries to access the 1st row of 0??? If you need some dummy row returned even in the case where there's no match, then you'll have to construct your query that way... -- Scott Ribe scott_r...@elevated-dev.com http://www.elevated-dev.com/ (303) 722-0567 voice -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: *****SPAM***** Re: [GENERAL] if-clause to an exiting statement
On Dec 7, 2010, at 9:58 AM, Kobi Biton wrote: > > I know it does not sound logic however I do need to set the row count > to 1 in case row count returns 0 Perhaps I didn't make myself clear: you can't do that. The only thing you can do is make sure your query returns a row, and in the case where it currently doesn't return a row I have absolutely no idea what it would be that you would need to return. If it would be acceptable to always return some hard-wired dummy row in addition to the 0 or more rows that match the current query, then you could use a UNION to add the dummy row to the selection. Otherwise, perhaps the real problem is that you do not have a matching event in the database and the real solution is to add such an event. In your original post you referred to an application bug where a trigger does not run if the row count is 0. It's hard for me to imagine how it's a bug to not take action when there is no event that needs processing... -- Scott Ribe scott_r...@elevated-dev.com http://www.elevated-dev.com/ (303) 722-0567 voice -- 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] UUID column as pimrary key?
On Jan 5, 2011, at 1:31 AM, Radosław Smogura wrote: > * simple to generate, and 128bit random is almost globally unique, Almost? Should be totally unique, as long as your random source is decent quality. -- Scott Ribe scott_r...@elevated-dev.com http://www.elevated-dev.com/ (303) 722-0567 voice -- 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] UUID column as pimrary key?
On Jan 5, 2011, at 7:28 AM, Radosław Smogura wrote: > It's simpler to write: ... > isn't it? Depends on the situation, the libraries you're using, and so on. Now, if you're generating records in a distributed system, where your node might be disconnected when it's creating a record, it is *much* simpler in that case ;-) -- Scott Ribe scott_r...@elevated-dev.com http://www.elevated-dev.com/ (303) 722-0567 voice -- 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] UUID column as pimrary key?
On Jan 5, 2011, at 7:55 AM, Grzegorz Jaśkiewicz wrote: > But I would never rely on that alone. You always have a strategy in > place, in case there's a duplicate. That's really unnecessary, basically a total waste of effort. -- Scott Ribe scott_r...@elevated-dev.com http://www.elevated-dev.com/ (303) 722-0567 voice -- 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] UUID column as pimrary key?
On Jan 5, 2011, at 8:03 AM, Mike Christensen wrote: > As long as all your UUIDs are generated with the same algorithm, they > are guaranteed to be unique. There is no requirement that they be generated with the same algorithm in order to be unique. A MAC/time-based UUID cannot duplicate a random one, and vice versa. (Also applies to the 3rd flavor of UUID whose details I do not remember.) -- Scott Ribe scott_r...@elevated-dev.com http://www.elevated-dev.com/ (303) 722-0567 voice -- 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] UUID column as pimrary key?
On Jan 5, 2011, at 8:05 AM, Bill Moran wrote: > Beyond that, the namespace size for a UUID is so incomprehensibly huge > that the chance of two randomly generated UUIDs having the same value > is incomprehensibly unlikely Yes, as in: it is *far* more likely that all of your team members and all of your client contacts will be simultaneously struck by lightning and killed in their sleep, and it is *far* more likely that all life on earth will be wiped out by an asteroid impact, and it is *far* more likely that the solar system orbits are not actually stable and earth will fly off into space... If you're worried about UUID collisions, then either your priorities are completely wrong, or you live in a bomb shelter--that's not sarcasm by the way, it's simply true, the chance of a collision is so vanishingly small that it is dwarfed by all sorts of risks that we all ignore because the chances are so low, including the chance that all drives in all your RAIDs across all your replicas will simultaneously fail on the same day that fires start in all the locations where your tapes are stored and all the sprinkler systems fail... (By "far" more likely, I mean many many many orders of magnitude...) > In the end, we chose b for the human > factor. A very good decision, in the case where you're actually able to control each independent system. > Face it, reading, remembering, and typing UUIDs kinda sucks. Lots of copy & paste, or custom GUI tools for devs & DBAs, or abuse like '...%', all of them painful in their own way. -- Scott Ribe scott_r...@elevated-dev.com http://www.elevated-dev.com/ (303) 722-0567 voice -- 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] UUID column as pimrary key?
On Jan 5, 2011, at 8:55 AM, Bill Moran wrote: > That statement demonstrates a lack of investigation and/or consideration > of the circumstances. No, it doesn't. > However, if there are 5000 devices generating 100 UUIDs per hour, and you'll > be keeping those records for 10+ years, the chances of collisions near > the end of that 10 year span get high enough to actually make developers > nervous. No, they don't. At the end of your hypothetical 10-year period, you will have used about 43,000,000,000 UUIDs, or about 1/100,000,000,000,000,000,000,000,000th of the UUID space (assuming random UUIDs). Leaving you with a chance of a single collision of about 1/18,000,000,000,000,000. Assuming of course good entropy. If the generation of random numbers is bad, then UUIDs are not so useful ;-) -- Scott Ribe scott_r...@elevated-dev.com http://www.elevated-dev.com/ (303) 722-0567 voice -- 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] UUID column as pimrary key?
On Jan 5, 2011, at 9:01 AM, Tom Lane wrote: > In practical use I think the odds of a collision are *far* higher than > you are suggesting, unless the UUID generation is being done with a lot > more care than is likely if the user takes these sorts of claims at face > value. Eh? The user taking such claims at face value has no bearing whatsoever on the quality of the UUID generation algorithm provided by the OS. So, unless we're talking about users coming up with their own algorithms, it seems reasonable to assume that the generation is done with a great deal of care. (And if we are talking about users coming up with their own algorithms, then all bets are off; feel free to assume the worst.) I know that is the case on OS X & Linux. I would be shocked if it were not the case on Solaris. I would even be surprised if it were not the case on Windows. The IETF Network Working Group designed UUIDs to ensure that their uniqueness guarantee would be strong enough that no application would need to worry about duplicates, ever. Claims that collisions are too likely to depend on UUIDs being unique really are claims that the IETF Network Working Group didn't know what it was doing, which I find a bit ridiculous. -- Scott Ribe scott_r...@elevated-dev.com http://www.elevated-dev.com/ (303) 722-0567 voice -- 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] UUID column as pimrary key?
On Jan 5, 2011, at 9:19 AM, Leif Biberg Kristensen wrote: > I can't help thinking of the «Birthday Paradox»: Yes, the calculation of the probability of a collision is the same for the "birthday paradox" as for random UUID collisions. -- Scott Ribe scott_r...@elevated-dev.com http://www.elevated-dev.com/ (303) 722-0567 voice -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: *****SPAM***** Re: [GENERAL] UUID column as pimrary key?
On Jan 5, 2011, at 10:30 AM, Radosław Smogura wrote:
> 128bits is huge for now, but what will happen in next 2,3 years?
It will still be large. When you get up to around 100 trillion UUIDs, you'll be
getting up to around a 1 in a billion chance of a single collision. Before you
claim that we'll outgrow them in a few years, you might want to consider the
actual numbers, the growth rate of storage density, the number of disks
required to hold that many records. I have a feeling the answers might surprise
you ;-)
> If we want to guarantee uniquness of UUID across calls, we could talk about
> much more far _pseudo_ random generator, then "normal" pseudo - randoms, and
> what I think we need to keep state of such random generator, and share and
> lock it for multiple concurrent calls. So it will not be something different
> then ordinal serial column...
No, we don't really have to think about it much at all. The IETF and OS
engineers already did. Random UUIDs are not generated with some sloppy
amateurish algorithm.
> My opinion about all of those UUID with MAC, IP addresses, Microsoft
> "growing"
> UUIDs. All of this decrases chance of uniqness of UUID.
Well, a decrease from one insanely small chance to another insanely small
chance is not anything to worry about. After all, you could argue that 128 bits
is a "decrease" from 256 bits. It's the same argument. UUIDs were designed to
avoid collisions, by people who knew what they were doing. More significant
bits would lower the chance, from "already low enough" to "even more low
enough".
> Shouldn't this be enaugh for namespace UUIDs
> new UUID("namespece".hashCode(), "name".hashChode())
>
> or a little joke...
> new UUID(1,1) meats this condition
>>o The UUIDs generated at different times from the same name in the
>>
>> same namespace MUST be equal.
>
People, people, people, please. *Namespace* UUIDs are intended to map *unique*
names to UUIDs in the case where you already have *unique* names and just need
to map them to a more compact form, thus the requirement that the same
namespace + name always yields the same UUID. This is *not* a weakness in
UUIDs, nor is it the kind of UUID you get from a simple uuid_gen or similar
call, nor is it a possible source of collisions for database UUIDs (unless you
do something enormously stupid, like use database fields to construct a name to
give to a UUID generator).
--
Scott Ribe
scott_r...@elevated-dev.com
http://www.elevated-dev.com/
(303) 722-0567 voice
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: *****SPAM***** Re: [GENERAL] UUID column as pimrary key?
On Jan 5, 2011, at 11:31 AM, Radosław Smogura wrote: > The true is that probability > that in two coin drops we will get two reverses is 1/4, but true is, too, as > Newton said, it's 1/3, because if in 1st drop we don't get reverse we don't > need to drop again. Nonsense. You don't stop generating UUIDs just because you haven't yet got a collision. -- Scott Ribe scott_r...@elevated-dev.com http://www.elevated-dev.com/ (303) 722-0567 voice -- 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] UUID column as pimrary key?
On Jan 5, 2011, at 12:03 PM, Bill Moran wrote: > For crying out loud. If you're going to pick me apart with numbers, then > actually do it with some intelligence. If you're going to get nasty, at least try to be accurate. > I could easily counter your argument by upping the numbers to 500,000 > mobile devices generating 1 UUIDs per hour over 20 years ... or raise > it even higher if you come back with that same argument ... Yeah, then you get into the realm of 1 in 10s of millions of a chance of collision. But you would need to explain to me how you would get that many records into the database committed to disk, when the UUIDs alone without any other data represent a stream of 22MB/s ;-) Or, looked at another way, inserting 1,388,889 rows/second would indeed be difficult to sustain. > But the point (that you are trying to sidestep) is that the UUID namespace > is finite, so therefore you WILL hit a problem with conflicts at some point. > Just because that point is larger than most people have to concern themselves > with isn't an invalidation. I'm not sidestepping the point at all. The point is that the finiteness of the space is a red herring. The space is large enough that there's no chance of collision in any realistic scenario. In order to get to a point where the probability of collision is high enough to worry about, you have to generate (and collect) UUIDs at a rate that is simply not realistic--as in your second example quoted above. If you just keep raising your numbers, you could go for 100,000,000,000,000 devices generating 100,000,000,000,000 UUIDs an hour for 10,000 years. Collisions would be guaranteed, but that does not make it a useful scenario to consider. 2^256 is a finite space as well. Would you argue that because it "is finite, so therefore you WILL hit a problem with conflicts at some point"? How about 2^512? (Bearing in mind that even though finite that space would be large enough to assign approximately 10^74 UUIDs to every atom in the observable universe, or 10^51 UUIDs to every atom in the total universe using high-end estimates of the size of the non-observable universe)? -- Scott Ribe scott_r...@elevated-dev.com http://www.elevated-dev.com/ (303) 722-0567 voice -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: *****SPAM***** Re: [GENERAL] UUID column as pimrary key?
On Jan 5, 2011, at 1:13 PM, Raymond O'Donnell wrote: > Is that taking dark matter into account? :-) It's not clear to me ;-) -- Scott Ribe scott_r...@elevated-dev.com http://www.elevated-dev.com/ (303) 722-0567 voice -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: *****SPAM***** Re: [GENERAL] UUID column as pimrary key?
On Jan 5, 2011, at 3:03 PM, Andrew Sullivan wrote: > ...the example was not that UUIDs are being generated and collected > in one place at that rate, but that they're being generated in several > independent places at a time... In order for a collision to matter, really in order for there to *be* a collision, the duplicate pair has to be collected in one place. -- Scott Ribe scott_r...@elevated-dev.com http://www.elevated-dev.com/ (303) 722-0567 voice -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: *****SPAM***** Re: [GENERAL] UUID column as pimrary key?
On Jan 5, 2011, at 4:11 PM, Michael Satterwhite wrote: > Each machine would have a unique machine_id. This would guarantee uniqueness > and be very easy to maintain. And if somebody clones the disk to a new machine, but leaves the old one in service? Or do you use the MAC address and hope that's unique? -- Scott Ribe scott_r...@elevated-dev.com http://www.elevated-dev.com/ (303) 722-0567 voice -- 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] UUID column as pimrary key?
On Jan 6, 2011, at 2:51 AM, Jasen Betts wrote: > Who was it that decided on 32 bits for IP addresses? Nice try, but that was rather long before the IETF existed ;-) -- Scott Ribe scott_r...@elevated-dev.com http://www.elevated-dev.com/ (303) 722-0567 voice -- 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] UUID column as pimrary key?
On Jan 6, 2011, at 1:52 AM, Stuart Bishop wrote: > If you are looking at these extreme > improbabilities, your SERIAL isn't guaranteed unique either when you > take into account cosmic rays flipping the right bits in your ECC > memory or on your disk platter. Yes, that's rather the point, the probability is so extremely low that it in most cases it should be treated as 0. Some people seem to have a problem wrapping their heads around relative magnitudes that extreme. -- Scott Ribe scott_r...@elevated-dev.com http://www.elevated-dev.com/ (303) 722-0567 voice -- 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] UUID column as pimrary key?
On Jan 6, 2011, at 8:14 AM, Bill Moran wrote: > I don't give a fuck how small the chance of conflict is, the only > viable option for that chance is 0. Period. Any argument to the > contrary is stupid, asinine and outright negligent. Do you give a fuck about the chance that bits will flip in the RAM and not be detected? Do you give a fuck about the chance that bits will flip in whatever persistent storage is in your device and not be detected? Do you give a fuck about the chance that bits will be flipped in the network and not be detected? Do you give a fuck about the chance that a transistor will go into a metastable state instead of flipping, and lock up the device and lose data not yet saved? Well, of course you do. But now what are the relative odds? All of these things can happen already (and all of them can happen the very first time you use it), so already your system is not precisely 100% reliable. Now is the risk of UUID collision somewhere near the same as these risks, or orders of magnitude higher, or orders of magnitude lower? It does matter. > However, you > should always take 5 or 10 minutes to consider whether your application > is one of the .001% that can't tolerate the tiny risk. If an application/device truly can't tolerate a risk of an error of 1 in 10^-16, that's a problem because you can't build a device without risk of error below some threshold (in that general neighborhood I think). You can only push risks to lower & lower probability, and it makes no sense to focus on a single risk and spend time and effort to push it to orders of magnitude lower probability than all the other risks in the system. (As long as there are risks at orders of magnitude higher priority, those should get the time & expense.) > And that's been my point all along, despite people trying to dilute it > with nonsense numbers that they don't understand... No, it hasn't been your point all along. Your point has shifted twice now as you've been shown to be wrong about the odds. And the numbers used are not nonsense at all. All of which somewhat contradicts your statement that your "head is totally wrapped around probability" ;-) Added to your apparent ignoring of other error sources in order to focus on one extremely unlikely one, well... > And also, if your entire solution to that risk is to rollback the > transaction in the event of a conflict, then your application is simple > enough that UUIDs are overkill anyway. I kind of doubt that the person who posted that intended it as the entire solution. It seemed to me that was intended as just the event that triggers conflict resolution and the next step would be to inform the device that the conflicting record is getting a new UUID, update appropriately, and so on. Just so you know, I'm done talking to you. Your arrogance, rudeness, insults, condescension and personal attacks are not something that I will deal with anymore. -- Scott Ribe scott_r...@elevated-dev.com http://www.elevated-dev.com/ (303) 722-0567 voice -- 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] UUID column as pimrary key?
On Jan 6, 2011, at 8:19 AM, Michael Satterwhite wrote: > That would be a matter of incompetent administration. *NOTHING* can protect > against that. Well, no, not necessarily. It might well be a goal (in fact, is a goal with some software that I'm developing), that users/admins don't have to worry about data caches moving across machines. My primary point, which I stated incompletely, was that in order to depend on node ids as part of unique ids, requires a degree of control over the administration of nodes, and for a given application this might or might not be practical. For instance, if your app runs on cell phones, and the OSs you deploy on give you access to the device id, and you don't mind using a rather long prefix to form your unique ids, then you have an obvious solution that, as far as I know, is guaranteed to be unique. (Ignoring the possibility of hacking of the device id, because no matter what you choose as a prefix, if an adversary manages to deliberately change the prefix, you can get duplicates.) My secondary point was that this is rather difficult to detect in time to prevent conflicts. -- Scott Ribe scott_r...@elevated-dev.com http://www.elevated-dev.com/ (303) 722-0567 voice -- 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] UUID column as pimrary key?
On Jan 6, 2011, at 9:31 AM, Chris Browne wrote: > The reasonable choices for a would-be artificial primary key seem to be > 1 and 3; in a distributed system, I'd expect to prefer 1, as the time + > host data are likely to eliminate the "oh, it might just randomly match" > problem. In some contexts, 1 is considered a security weakness, as it reveals information about which machine generated it and when, which is why most OS-supplied uuid generators now default to 4 (random). This tends to be more of a concern with encryption/security uses, and if it's not a concern for your db[*], then your are correct that 1 is likely the best choice. [*] After all, in many dbs we log all sorts of explicit where/who/when for auditing purposes. In that case, having ids that provide a clue of where/when most certainly does not add any legitimate security concern. -- Scott Ribe scott_r...@elevated-dev.com http://www.elevated-dev.com/ (303) 722-0567 voice -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] finding bogus UTF-8
I know that I have at least one instance of a varchar that is not valid UTF-8, imported from a source with errors (AMA CPT files, actually) before PG's checking was as stringent as it is today. Can anybody suggest a query to find such values? -- Scott Ribe scott_r...@elevated-dev.com http://www.elevated-dev.com/ (303) 722-0567 voice -- 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] Get column list from Postgres Query
On Feb 19, 2011, at 4:42 PM, PANAGIOTIS GERMANIS wrote: > want to get the names, the type and the length of all columns in a postgres > query using c-api calls. This documented pretty clearly in the libpq docs: <http://www.postgresql.org/docs/9.0/static/libpq.html> -- Scott Ribe scott_r...@elevated-dev.com http://www.elevated-dev.com/ (303) 722-0567 voice -- 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] Mac OSX 10.6 libpq.5.dylib
On Feb 19, 2011, at 4:09 AM, PANAGIOTIS GERMANIS wrote: > Dyld Error Message: > Library not loaded: libpq.5.dylib > Referenced from: > /Volumes/Documents/Projects/Mac_Software/iMacProjects/macReport/build/Release/macReport.app/Contents/MacOS/macReport > Reason: image not found There are two ways I know of to get that error: - The dylib is being looked for at the wrong location, which is of course what the install_name_tool command is about. You need to understand what you're actually doing with that command, what the library dependencies actually are and so on, and make sure you're doing it right. - The dylib might be located, but might not contain the correct architecture. You need to make sure you're building a fat lib that includes all architectures that your app will support. -- Scott Ribe scott_r...@elevated-dev.com http://www.elevated-dev.com/ (303) 722-0567 voice -- 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] Notify rule
On Tue, Feb 22, 2011 at 1:03 AM, ivan_14_32 wrote: > Hi! > I trying to create rule for update notifing: > > CREATE OR REPLACE RULE "ttt_NotifyU" AS > ON UPDATE TO "ttt" DO > NOTIFY "ttt", '88'; > this work. > > But this > CREATE OR REPLACE RULE "ttt_NotifyU" AS > ON UPDATE TO "ttt" DO > NOTIFY "ttt", NEW."id"; > don't (syntax error), > NOTIFY "ttt", CAST(NEW."id" as text) > too. > > Q: How can I send tuple id (integer primary key) via notify using Rule? > Target is erasing deprecated tuples from application's cache. Well, that second argument to NOTIFY must be a literal. If you need to notify with a dynamic value, then you need to use the pg_notify function instead--regardless of whether you use a rule or a trigger. -- Scott Ribe scott_r...@elevated-dev.com http://www.elevated-dev.com/ (303) 722-0567 voice -- 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 extract a value from a record using attnum or attname?
I don't know if you can quite write the generalized notification function you want in plpgsql or not, but you can certainly write the meta-function that create the function for any table ;-) -- Scott Ribe scott_r...@elevated-dev.com http://www.elevated-dev.com/ (303) 722-0567 voice -- 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] PostgreSQL database design for a large company
On Feb 25, 2011, at 6:24 PM, Craig Ringer wrote: > In reality you must plan periodic updates, in which case it's quite likely > that the growth of hardware and software performance will keep up with the > data growth. I've been in a lovely little niche that has fit that description since 1993 ;-) -- Scott Ribe scott_r...@elevated-dev.com http://www.elevated-dev.com/ (303) 722-0567 voice -- 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: invalid byte sequence for encoding "UTF8": 0xc35c
On Feb 27, 2011, at 5:47 AM, AI Rumman wrote: > Any idea please. Don't do that ;-) Seriously, the error means exactly what it says, so you have to figure out why your app is trying to insert invalid UTF-8. -- Scott Ribe scott_r...@elevated-dev.com http://www.elevated-dev.com/ (303) 722-0567 voice -- 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] Thoroughly confused about time zones
On Feb 28, 2011, at 8:06 AM, Rob Richardson wrote: > But if PostgreSQL doesn’t store time zones internally, then that difference > is going to be 24 hours, which doesn’t help me. No, postgres stores timestamptz as UTC, so that calculation will work exactly like you want. -- Scott Ribe scott_r...@elevated-dev.com http://www.elevated-dev.com/ (303) 722-0567 voice -- 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] Thoroughly confused about time zones
On Feb 28, 2011, at 8:18 AM, Rob Richardson wrote:
> But when I tried this:
>
> select cast (extract(epoch from ('2010-3-14 12:00'::timestamptz - '2010-3-13
> 12:00'::timestamptz)) as integer) / 60 / 60
>
> I got 23, showing that even if I did not specify what time zone I’m talking
> about, I got the correct answer.
You didn't specify the time zone, so it used your local time zone info--but not
just your current offset from UTC, rather the offsets from UTC at the
dates/times specified.
--
Scott Ribe
scott_r...@elevated-dev.com
http://www.elevated-dev.com/
(303) 722-0567 voice
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: *****SPAM***** [GENERAL] Index question
On Mar 2, 2011, at 11:31 AM, Michael Black wrote: > Ok. I have been working with databases a few years but my first real venture > in to PostgreSql. I just want a plain simple index regardless if there are > duplicates or not. How do I accomplish this in PostgreSql? Same as any other SQL database: create index foobaridx on foo(bar)... -- Scott Ribe scott_r...@elevated-dev.com http://www.elevated-dev.com/ (303) 722-0567 voice -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Index question
On Mar 2, 2011, at 11:43 AM, Michael Black wrote: > Thanks Scott. I just did not see the options in the PGAdmin III nor in the > doc at You may want to bookmark this: <http://www.postgresql.org/docs/9.0/static/sql-commands.html> -- Scott Ribe scott_r...@elevated-dev.com http://www.elevated-dev.com/ (303) 722-0567 voice -- 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] NULL value vs. DEFAULT value.
On Mar 8, 2011, at 7:54 AM, James B. Byrne wrote: > My question is: Why am I getting a NULL exception? Because you're trying to insert NULL explicitly? -- Scott Ribe scott_r...@elevated-dev.com http://www.elevated-dev.com/ (303) 722-0567 voice -- 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] Primary key vs unique index
On Mar 17, 2011, at 6:19 AM, Voils, Steven M wrote: > Is there a fundamental difference between a primary key and a unique index? > Currently we have primary keys on tables that have significant amounts of > updates performed on them, as a result the primary key indexes are becoming > significantly bloated. There are other indexes on the tables that also > become bloated as a result of this, but these are automatically rebuild > periodically by the application (using the concurrently flag) when read usage > is expected to be very low. > > We don’t want to remove the unique constraint of that the primary key is > providing, but the space on disk will continue to grow unbounded so we must > do something. Can we replace the primary key with a unique index that could > be rebuilt concurrently, or would this be considered bad design? The > reasoning behind this would be that the unique index could be rebuilt > concurrently without taking the application down or exclusively locking the > table for an extending period of time. Are there other advantages to a > primary key outside of a uniqueness constraint and an index? So, you can't use REINDEX because it lacks the CONCURRENTLY option, and that would lock too much for too long? The only thing the primary key designation provides beyond not null & unique is the metadata about what is the primary key. Which for example in the db allows foreign key constraints to be created without specifying that column. And some ORM/apps/frameworks can automatically make use of the information as well. I like having them for clarity, but you really can do away with them if your deployment needs to do so. -- Scott Ribe scott_r...@elevated-dev.com http://www.elevated-dev.com/ (303) 722-0567 voice -- 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] query execution time
On Mar 21, 2011, at 9:55 AM, preetika tyagi wrote: > For example, if A is 15 minutes, then B is 1.5 hrs. Well, considering that random disk access is on the order of 10,000 times slower than RAM... But you can answer the question yourself by comparing the query run against cold caches (after a reboot, or various command-line tricks to purge cache) vs against warm caches (twice back-to-back). -- Scott Ribe scott_r...@elevated-dev.com http://www.elevated-dev.com/ (303) 722-0567 voice -- 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] query execution time
On Mar 21, 2011, at 12:03 PM, preetika tyagi wrote: > I tried running the same query after reboot and back-to-back, it was taking > less time in both the cases. It means the problem is something else. > > Can there be a reason which is more hardware/operating system specific and > due to which the behavior is not uniform? While I do have a couple of ideas, you're probably better served by letting those here with more optimization experience help you, as their answers will be more complete. -- Scott Ribe scott_r...@elevated-dev.com http://www.elevated-dev.com/ (303) 722-0567 voice -- 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] Trigger Function return values
On Mar 22, 2011, at 4:12 PM, Andy Chambers wrote: > How is the return value of a trigger function defined in plpgsql used? I > can't find > anything in the documentation but some of the examples return NULL, and > others return > something like NEW. <http://www.postgresql.org/docs/9.0/static/trigger-definition.html> -- Scott Ribe scott_r...@elevated-dev.com http://www.elevated-dev.com/ (303) 722-0567 voice -- 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] temp tables not dropping at end of script
On Apr 6, 2011, at 9:47 AM, Davenport, Julie wrote: > We’ve never explicitly closed the connection, it just seemed to close > automatically when the coldfusion script ended. My guess is you've also upgraded coldfusion, or changed its config, and now it's caching connections. -- Scott Ribe scott_r...@elevated-dev.com http://www.elevated-dev.com/ (303) 722-0567 voice -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] 9.2 upgrade glitch with search_path
Built & installed 9.2.3. Dumped 9.1 db (using 9.2 pg_dump IIRC). Restored. Database search path was not restored. Had to execute alter database ... set search_path to... Dump commands: pg_dumpall -g -f roles.dump pg_dump -F c -Z 0 -v pedcard > db.dump Restore commands: psql -f roles.dump postgres pg_restore -j 4 -veC -d postgres db.dump -- Scott Ribe scott_r...@elevated-dev.com http://www.elevated-dev.com/ (303) 722-0567 voice -- 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] 9.2 upgrade glitch with search_path
On Jan 13, 2013, at 2:51 PM, Tom Lane wrote: > That's a hole in the particular dump methodology you selected: > >> pg_dumpall -g -f roles.dump >> pg_dump -F c -Z 0 -v pedcard > db.dump > > pg_dump does not dump/restore database properties, only database > contents. Properties are the responsibility of pg_dumpall, which > you bypassed (for databases anyway). > > There's been some discussion of refactoring these responsibilities, > but no consensus. Ah, this is my first upgrade using that methodology, in order to get concurrent restore functionality. Prior to this I've always used pg_dumpall. -- Scott Ribe scott_r...@elevated-dev.com http://www.elevated-dev.com/ (303) 722-0567 voice -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] seeking SQL book recommendation
For a client who needs to learn how to query the db: - No SQL knowledge at all; needs to start from square 1. - Smart, capable person, who will be in this position for a long time, using this db for a long time. - No chance in hell this db will be moved off PG, so PG-centric is fine ;-) -- Scott Ribe scott_r...@elevated-dev.com http://www.elevated-dev.com/ (303) 722-0567 voice -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] update performance of degenerate index
I'm seeing occasional simple-looking updates take way longer than I think they should, and if my theory about it is correct, it's not actually a problem. Consider this index, intended to provide extremely quick access to a small number of items from a much larger table: create index not_exported on exports(id) where exported_when is null My guess is that if instead of a very small number of items, there are 1000s or 10s of 1000s of items, and a process is updating them one at a time, then occasionally there will be an expensive update of that index that involves touching & writing a lot of pages? If that's what's happening, great. (The processing is normally triggered by notify, and happens much faster than the rate at which these come in, so the number of items in that index should be 0 most of the time, occasionally 1 for a second, and possibly but rarely 2 or 3 for a second. The current situation of lots of entries in it has to do with 1-time processing of legacy data.) If that can't be what's happening, then I would want to investigate further why an update of a smallish row with 3 small indexes sometimes takes 600ms. -- Scott Ribe scott_r...@elevated-dev.com http://www.elevated-dev.com/ (303) 722-0567 voice -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] why does this not give a syntax error?
pg 9.2: delete from "ExternalDocument" where id = 11825657and "Billed" = 'f'; -- Scott Ribe scott_r...@elevated-dev.com http://www.elevated-dev.com/ (303) 722-0567 voice -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] why does this not give a syntax error?
On Jun 10, 2013, at 12:52 PM, Tom Lane wrote: > Scott Ribe writes: >> pg 9.2: >> delete from "ExternalDocument" where id = 11825657and "Billed" = 'f'; > > "11825657and" is not any more lexically ambiguous than "11825657+". > It has to be two separate tokens, and that's how it's read. But it's not read correctly. In other words: delete from "ExternalDocument" where id = 11825657and "Billed" = 'f'; deleted 0 rows, while: delete from "ExternalDocument" where id = 11825657 and "Billed" = 'f'; deleted 1 row. ??? -- Scott Ribe scott_r...@elevated-dev.com http://www.elevated-dev.com/ (303) 722-0567 voice -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] PG on SSD
Is it reasonable to run PG on a mirrored pair of something like the Intel SSD DC 3610 series? (For example: http://ark.intel.com/products/82935/Intel-SSD-DC-S3610-Series-480GB-2_5in-SATA-6Gbs-20nm-MLC) I'd *hope* that anything Intel classifies as a "Data Center SSD" would be reasonably reliable, have actually-working power loss protection etc, but is that the case? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] the "PgJDBC driver is not an executable" message
Well you know the old saying: "the road to hell is paved with good intentions"! So an attempt was made to help people who download the JDBC driver and then try to "run" it: https://github.com/pgjdbc/pgjdbc/pull/112/files But here's a fun side effect: If you add a .jar to an Eclipse project that has a main(), Eclipse adds a new run configuration with that class as the main class, and further makes that the current selected config, automatically and silently. Took me a little bit to figure that one out... (because I'm a novice Eclipse user...) So, is it worth adding some explanatory text? It was really annoying to suddenly start getting this message when I never had any intention of "running" the JDBC driver ;-) -- Scott Ribe scott_r...@killerybtes.com (303) 722-0567 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] RemoveIPC problem
Thanks to a typo, I did not turn off systemd's RemoveIPC, and had many many pg restarts before I figured out the problem. Should my data be OK? Or do I need to dump & reload? -- Scott Ribe scott_r...@elevated-dev.com (303) 722-0567 -- 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] RemoveIPC problem
Yeah, I was kind of thinking that PG detects the semaphore not existing, bails immediately, restarts clean, thus no problem. I just wanted to hear from people, like you, that know way more than I do about the internals. > On Aug 31, 2017, at 9:08 PM, Tom Lane wrote: > > scott ribe writes: >> Thanks to a typo, I did not turn off systemd's RemoveIPC, and had many many >> pg restarts before I figured out the problem. > >> Should my data be OK? Or do I need to dump & reload? > > I don't know of any reason to think that that poses a data corruption > risk. (But I've been wrong before.) > > regards, tom lane -- Scott Ribe scott_r...@elevated-dev.com (303) 722-0567 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
