[GENERAL] scope quirk in copy in function

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 -- S

[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/

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.*')

Re: [GENERAL] quick q re execute & scope of new

tring 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

[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

[GENERAL] different empty array syntax requirements

) 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

Re: [GENERAL] different empty array syntax requirements

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 maili

Re: [GENERAL] different empty array syntax requirements

n 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-gener

Re: [GENERAL] Uhm, so, yeah, speaking of /.

quot;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

Re: [GENERAL] why postgresql over other RDBMS

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

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 ---

Re: [GENERAL] Corruption of files in PostgreSQL

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

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

Re: [GENERAL] an other provokative question??

lized 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

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

ess 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 wher

[GENERAL] RETURN NEXT on result set

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)-

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 ---(e

Re: [GENERAL] Inheritance fixing timeline? (Was "Inherited FK Indexing")

thing 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

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/

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-0

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

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

Re: [GENERAL] Linux v.s. Mac OS-X Performance

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 benc

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

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 PR

Re: [GENERAL] Linux v.s. Mac OS-X Performance

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

ok 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)

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/

Re: [GENERAL] Linux v.s. Mac OS-X Performance

rmance 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. -

Re: [GENERAL] What was new in 8.4 & 8.3?

ople 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.elevat

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-gene

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 (pg

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.co

Re: [GENERAL] Why facebook used mysql ?

s 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.post

[GENERAL] identifying local connections

main 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

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) 7

Re: [GENERAL] identifying local connections

etstat 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

versight 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.

[GENERAL] figuring out a streaming replication failure

ssible 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:

Re: [GENERAL] Whose bug is this?

nd 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

[GENERAL] limits of constraint exclusion

d 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 h

Re: [GENERAL] alter table add column - specify where the column will go?

ity 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

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 ma

Re: [GENERAL] Looking for auto starting procedures

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 Rib

Re: *****SPAM***** Re: [GENERAL] if-clause to an exiting statement

ly 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

Re: *****SPAM***** Re: [GENERAL] if-clause to an exiting statement

;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

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-0

Re: [GENERAL] UUID column as pimrary key?

#x27;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.

Re: [GENERAL] UUID column as pimrary key?

cate 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 t

Re: [GENERAL] UUID column as pimrary key?

& 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 mak

Re: [GENERAL] UUID column as pimrary key?

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.eleva

Re: [GENERAL] UUID column as pimrary key?

n'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...@ele

Re: *****SPAM***** Re: [GENERAL] UUID column as pimrary key?

id_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?

. 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 su

Re: [GENERAL] UUID column as pimrary key?

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-gene

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@p

Re: *****SPAM***** Re: [GENERAL] UUID column as pimrary key?

r 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 subs

Re: *****SPAM***** Re: [GENERAL] UUID column as pimrary key?

d 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-gene

Re: [GENERAL] UUID column as pimrary key?

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

Re: [GENERAL] UUID column as pimrary key?

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, condesc

Re: [GENERAL] UUID column as pimrary key?

ibility 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...@

Re: [GENERAL] UUID column as pimrary key?

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

[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

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

Re: [GENERAL] Mac OSX 10.6 libpq.5.dylib

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.el

Re: [GENERAL] Notify rule

mary 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

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

Re: [GENERAL] PostgreSQL database design for a large company

cription 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)

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. -- Scot

Re: [GENERAL] Thoroughly confused about time zones

fy 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 htt

Re: *****SPAM***** [GENERAL] Index question

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/mailp

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://

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 mail

Re: [GENERAL] Primary key vs unique index

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:/

Re: [GENERAL] query execution time

ter 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 y

Re: [GENERAL] query execution time

t; 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/

Re: [GENERAL] Trigger Function return values

sql.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.

[GENERAL] 9.2 upgrade glitch with search_path

es.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/mailpre

Re: [GENERAL] 9.2 upgrade glitch with search_path

estore 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

;-) -- 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

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.

[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 chan

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+".

[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

[GENERAL] the "PgJDBC driver is not an executable" message

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.o

[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 (pgsq

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 rib

  1   2   3   4   >