Re: [GENERAL] a question for the way-back machine
Ben wrote: When you insert a tuple, it needs to be inserted into the index, yes. There is no way an insert can cause a sequential scan, except by some trigger defined on the table. Actually, as it happens, there *is* a trigger defined on the table to fire before insert, but it too uses an index scan, at least according to explain. Though, you'd think if it actually was using an index scan, that would be showing up in pg_stat_user_tables, which it isn't. Might the fact that the trigger is a plpgsql function be throwing it off and keeping it from using more recent planner stats? The query-plan for the function will be compiled first time it is called. From that point on, it is fixed. It seems that is the source of your seq-scans. You can use the EXECUTE statement to construct a dynamic version of the query, which will be planned every time it is run. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] about the RULE system
On Wed, 2006-12-13 at 15:43 -0600, Scott Marlowe wrote:
> On Wed, 2006-12-13 at 15:36, Rafal Pietrak wrote:
> >
> >
> > "REVOKE ALL ON FUNCTION piti() FROM PUBLIC"
> >
> > Doe not seam to have any effect on functions installed as a trigger.
>
> Does your "common user" have the permission to create users?
No (although the one I've initially tested this scenario on, was in a
group that did have that permission).
But this time I've tested this on user "niby":
\du niby
List of roles
Role name | Superuser | Create role | Create DB | Connections | Member
of
---+---+-+---+-+---
niby | no| no | no| no limit|
(1 row)
CREATE TEMP TABLE mini (id int, name text, fix int, emul text);
CREATE TRIGGER adad BEFORE INSERT ON mini FOR EACH ROW EXECUTE
PROCEDURE piti();
INSERT INTO mini (name,emul,id,fix) VALUES
('cz_'||(random()*100)::integer,'jasdklad', 130003, 1012 );
INSERT 0 1
--
where piti() is the function I've described earlier, and did REVOKE ALL
on that function.
*all* the above statements are executed as user "niby". The SCHEMA has:
REVOKE ALL; GRANT USAGE; It *does*not* have "GRANT CREATE".
I hope you can copy the results.
--
-R
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] MySQL drops support for most distributions
On Dec 13, 2006, at 11:21 AM, Tom Lane wrote: Markus Schiltknecht <[EMAIL PROTECTED]> writes: John D. Burger wrote: Sure, but they won't use PG either, for essentially the same reason, since =all= PG support is "third party". So one can debate if i.e. EnterpriseDB is providing third party support for PostgreSQL or first-hand support for their own product :-) The other point I'd make against John's argument is that there are a whole lot of Fortune 500 companies buying Red Hat support, and RH is effectively a third party for large chunks of Linux. (Of course, there are also large chunks for which Red Hat employees write as much code as anyone; but certainly that's not true for every package.) I think the real criterion for big companies is not so much whether you're supporting your "own" product as whether you're big enough to be worth suing if things go wrong. We sell a postgresql-based product into some very large, household name US and international, companies. In some cases we've been the first postgresql instance into otherwise Oracle or MySQL focused companies. I'm pretty sure we're smaller than any of the third-party postgresql support companies, so we'd be far less interesting to sue too. Cheers, Steve ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] MySQL drops support for most distributions
On Wed, 2006-12-13 at 14:21 -0500, Tom Lane wrote: > I think the real criterion for big companies is not so much whether > you're supporting your "own" product as whether you're big enough to > be worth suing if things go wrong. This is a common misunderstanding and it is incorrect, at least in my experience. I work at a company with >10K people. I oversee computer architecture and operations for Research (~800 people) and I work very closely with our large IT group. In order to understand how we purchase hardware, software, or support, you have to understand what's important to us. A successful company must focus on their products and not irrelevant details about how they gets produced and delivered. Employees may personally care about the detailed means to product, but successful companies and their managers -- and, ultimately, customers and stock holders -- do not. The major concerns for our purchases include: 1) Does it meet our functional requirements? 2) Does it integrate with our existing infrastructure? 3) Can we identify a support channel? and 4) What's the risk relative to other options? These days, OSS packages frequently exceed functional requirements over proprietary alternatives. Apache is an irrefutable example. Big vendors often have proven track records for (2) and (3), but it's not the bigness per se that appeals. We choose small vendors when that's appropriate for a need. Whom we sue when things go wrong is almost never a consideration during purchasing. If a relationship goes south, a suit is unlikely to address our primary goal, the product. Now, lest you think I'm a corporate troll on the pg lists, I should tell you that I'm probably among the most visible and vocal open source supporters here. I've long railed against proprietary software -- not because of support issues but because I view *some* proprietary software as a real threat to our long-term success. What's important is that our data are usable in ways we see fit, without encumbrance from vendors. This is not the goal of big vendors who require depend on lock-in for growth. The EnterpriseDB folks have the right strategy. Nobody wants Oracle itself, but rather they want database services that behave like Oracle (er, except the parts that annoy). If I can't tell that I'm not talking to Oracle but getting the "right" answers, why should I care? Cheaper too? Even better. Oracle should be scared because it seems inevitable that their database business will be commoditized out of existence. Concern for risk is perhaps the most elusive problem for OSS providers and supporters. Companies don't like risk, and *any* change to a working process is a risk. Much to my chagrin, this risk makes it difficult to unseat even mediocre products. We should all cheer EnterpriseDB's success in booking some big name companies. Their successes will establish PostgreSQL as a reliable, cost-effective, and empowering alternative to proprietary databases and therefore decrease the risk concerns. The only reason I spent this much time weighing in is because I'm thrilled with PostgreSQL (er, sorry Tom, Postgres) and appreciate and respect the terrific work done in this community. Thank you. Cheers, Reece -- Reece Hart, http://harts.net/reece/, GPG:0x25EC91A0 ./universe -G 6.672e-11 -e 1.602e-19 -protonmass 1.673e-27 -uspres bush kernel warning: universe consuming too many resources. Killing. universe killed due to catastrophic leadership. Try -uspres carter.
Re: [GENERAL] a question on SQL
Bruce Momjian wrote: > > Tom Lane wrote: > > "Greg Sabino Mullane" <[EMAIL PROTECTED]> writes: > > >> It's the single most useful non-standard SQL feature postgresql has. It > > >> is thus simultaneously bad (from a portatbility aspect) and brilliant > > >> (because it's a million times easier and faster than the alternatives). > > > > > You mean second-most useful. LIMIT/OFFSET is the champion, hand down. :) > > > > Yeah, but that one's only quasi-non-standard ... several other DBMSes > > have it too. > > I know MySQL has it, and SQL Lite added it. Which other ones? Someone > asked me recently. I see this chart from Perl documentation: > > > > http://search.cpan.org/~davebaird/SQL-Abstract-Limit-0.12/lib/SQL/Abstract/Limit.pm#DESCRIPTION > > Oh, and Rasmus Lerdorf told me he invented LIMIT for mSQL, and MySQL > then added it, and that MySQL added the limit option. > > This was interesting in the MySQL manuals: > > For compatibility with PostgreSQL, MySQL also supports the LIMIT > row_count OFFSET offset syntax. > > Did we add the OFFSET _keyword_. I remember we had the comma-ed numbers > backwards, and we had OFFSET, but I thought that keyword came from > MySQL. Obviously, they don't think so. Informix provides the "FIRST" syntax to get the leading rows of a set; I think you have to use cursors to get further offsets though (been a while since I have had to use it), e.g. "SELECT FIRST 10 col1, col2, col3 FROM foo WHERE ...". No "LAST" either (just tried). They have had this since at least IDS 8 and I thing the 7.x series had it as well. No idea where they got it from; I learned on Informix so I actually thought it was standard, until reality disabused me of the notion. Greg Williamson DBA GlobeXplorer LLC ---(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] selinux/postgresql/jfs
Richard P. Welty wrote: i'm working with an FC5 test server which i'm trying to get set up so we can benchmark xfs vs. jfs vs. ext3(writeback). does anyone have any suggestions on setting up a tablespace on a jfs partition with selinux in enforcing mode? following myself up here, as i made significant progress after posting that. i certainly have been learning a lot on this one. the relevant issue is that jfs doesn't support the attributes used by selinux in tagging files as part of its security model; xfs and ext3 do. some claim (including some selinux faqs) that this means you can't use jfs with selinux. this isn't actually true, but what is true is that you can't get most of the selinux security benefits with jfs. so for the celery postgresql test server, what i have is an ext3 boot partition (fedora won't permit xfs or jfs there), xfs for the system partitions, and jfs, xfs, and ext3/wb partitions on LVM stripe sets over pairs of disks. xfs and ext3 seem to work just fine with postgresql tablespaces out of the box with selinux in enforcing mode. jfs does not. in working through the selinux audit2allow steps, i came up with an selinux policy file that permits postgresql to set up a table space on a jfs partition, but it's extremely permissive and probably no more secure than just excluding postgresql from selinux security altogether. now, on to the benchmarks. richard -- Richard Welty[EMAIL PROTECTED] 1-866-MY-CELERY 518-269-8232 (cell) ---(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] MySQL drops support for most distributions
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 12/13/06 20:05, Gregory S. Williamson wrote: > FWIW, there is a follow-up note on the original posting from a > MySQL person: > > "we are just starting to roll out [Enterprise] binaries... We > don't build binaries for Debian in part because the Debian > community does a good job themselves... If you call MySQL and you > have support we support you if you are running Debian (the same > with Suse, RHEL, Fedora, Ubuntu and others)... someone in Sales > was left with the wrong information" Oh, darn! - -- Ron Johnson, Jr. Jefferson LA USA Is "common sense" really valid? For example, it is "common sense" to white-power racists that whites are superior to blacks, and that those with brown skins are mud people. However, that "common sense" is obviously wrong. -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD4DBQFFgLliS9HxQb37XmcRApMPAJ90kyMrGrifpzC0cJTgzfNKkAQhzwCYtNn0 UnCPrUfmaIvSx1btNF0jSg== =TxxO -END PGP SIGNATURE- ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] a question on SQL
Tom Lane wrote: > "Greg Sabino Mullane" <[EMAIL PROTECTED]> writes: > >> It's the single most useful non-standard SQL feature postgresql has. It > >> is thus simultaneously bad (from a portatbility aspect) and brilliant > >> (because it's a million times easier and faster than the alternatives). > > > You mean second-most useful. LIMIT/OFFSET is the champion, hand down. :) > > Yeah, but that one's only quasi-non-standard ... several other DBMSes > have it too. I know MySQL has it, and SQL Lite added it. Which other ones? Someone asked me recently. I see this chart from Perl documentation: http://search.cpan.org/~davebaird/SQL-Abstract-Limit-0.12/lib/SQL/Abstract/Limit.pm#DESCRIPTION Oh, and Rasmus Lerdorf told me he invented LIMIT for mSQL, and MySQL then added it, and that MySQL added the limit option. This was interesting in the MySQL manuals: For compatibility with PostgreSQL, MySQL also supports the LIMIT row_count OFFSET offset syntax. Did we add the OFFSET _keyword_. I remember we had the comma-ed numbers backwards, and we had OFFSET, but I thought that keyword came from MySQL. Obviously, they don't think so. -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] MySQL drops support for most distributions
FWIW, there is a follow-up note on the original posting from a MySQL person: "we are just starting to roll out [Enterprise] binaries... We don't build binaries for Debian in part because the Debian community does a good job themselves... If you call MySQL and you have support we support you if you are running Debian (the same with Suse, RHEL, Fedora, Ubuntu and others)... someone in Sales was left with the wrong information" Greg Williamson DBA GlobeXplorer LLC -Original Message- From: [EMAIL PROTECTED] on behalf of Scott Marlowe Sent: Wed 12/13/2006 10:11 AM To: Alvaro Herrera Cc: David Goodenough; pgsql general Subject:Re: [GENERAL] MySQL drops support for most distributions On Wed, 2006-12-13 at 12:01, Alvaro Herrera wrote: > Scott Marlowe wrote: > > On Wed, 2006-12-13 at 10:50, David Goodenough wrote: > > > http://developers.slashdot.org/article.pl?sid=06/12/13/1515217&from=rss > > > > > > "MySQL quietly deprecated support for most Linux distributions on October > > > 16, > > > when its 'MySQL Network' support plan was replaced by 'MySQL Enterprise.' > > > MySQL now supports only two Linux distributions — Red Hat Enterprise > > > Linux > > > and SUSE Linux Enterprise Server. We learned of this when MySQL declined > > > to > > > sell us support for some new Debian-based servers. Our sales rep 'found > > > out > > > from engineering that the current Enterprise offering is no longer > > > supported > > > on Debian OS.' We were told that 'Generic Linux' in MySQL's list of > > > supported > > > platforms means 'generic versions of the implementations listed above'; > > > not > > > support for Linux in general." > > > > So, in a similar vein, which PostgreSQL support companies support > > Debian, for instance? > > I bet Credativ does. > > The good thing is that there are several companies supporting Postgres, > so whatever one of them does it does not affect the market as a whole. I was kinda thinking the same thing. Man, must suck to be tied to the one true company for your database when they stop supporting your OS etc... And what about MySQL windows flavor? ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings --- Click link below if it is SPAM [EMAIL PROTECTED] "https://mailscanner.globexplorer.com/dspam/dspam.cgi?signatureID=458041d0161931045513543&[EMAIL PROTECTED]&retrain=spam&template=history&history_page=1" !DSPAM:458041d0161931045513543! --- ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] forcing compression of text field
Tom Lane wrote:
> Scott Marlowe <[EMAIL PROTECTED]> writes:
> > On Mon, 2006-12-11 at 10:18, Jonathan Ellis wrote:
> >> I have a table of log messages. They are mostly in the 100-200
> >> character length, which apparently isn't large enough for PG to want
> >> to compress it (length == octet_length). I really need to save disk
> >> space. I can store it as a bytea and compress it manually (zlib level
> >> 1 compression gives about 50% savings), but is there a way to force
> >> pg's own compression before I resort to this?
>
> > http://www.postgresql.org/docs/8.1/interactive/storage-toast.html
> > Has all your answers.
>
> The bottom line is that PG doesn't bother trying to compress values
> less than about 2KB long. While you could make a custom build with a
> different threshold, the fact remains that LZ-style compression is not
> real efficient on short stretches of text. If you "really need to save
> disk space" it behooves you to consider that. I'd suggest thinking about
> whether you can merge multiple log entries, or something, such that the
> field values you need to store are on the order of a few KB.
See ALTER TABLE ALTER [ COLUMN ] column SET STORAGE { PLAIN | EXTERNAL |
EXTENDED | MAIN }.
--
Bruce Momjian [EMAIL PROTECTED]
EnterpriseDBhttp://www.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
---(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] Slow query in 8.2.0
Manuel Sugawara writes: > Tom Lane <[EMAIL PROTECTED]> writes: >> 8.2 is a good bit more conservative than previous releases about >> optimizing views involving volatile functions, so I'm wondering whether >> that could have anything to do with it ... > Just out of curiosity, why the change? People complained about getting wrong/surprising answers due to unexpected multiple evaluations of volatile functions. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Slow query in 8.2.0
Tom Lane <[EMAIL PROTECTED]> writes: > 8.2 is a good bit more conservative than previous releases about > optimizing views involving volatile functions, so I'm wondering whether > that could have anything to do with it ... Oh well, you are indeed right, I went through the whole view hierarchy and replaced all volatile function calls (I used to use a trick defining a function to avoid a join that used to perform better in earlier versions) with explicit JOINs, also I relabeled some functions that were indeed STABLE and now everything is working fine. I was amazed that some of those views predates Postgres support of JOINs, I guees we have been using Postgres for a very long time :-). Just out of curiosity, why the change? Regards, Manuel. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] ALTER TABLE RENAME and primary key
Raymond O'Donnell wrote: > On 9 Dec 2006 at 18:22, brian wrote: > > > I'd like to rename a table but am unsure how this will affect the > > primary key. That is, the table currently being named 'service', it has a > > pkey named 'service_id_pkey'. I'd like to change the table name to > > 'service_provider' (which would mean i'd get 'service_provider_id_pkey'). > > I just tried it on 8.2, and it seems that renaming the table doesn't > rename the primary key index - you have to do it explicitely. > > > There's a second table that has a foreign key constraint on > > service_id_pkey, so do i need to drop that constraint first, rename the > > first table, then re-create the constraint using the new pkey name? > > Without having tried it, I don't think so - my understanding is that > database objects are represented internally as OIDs, so renaming the > object changes only the name and not the OID. We worked very hard to prevent renaming from affecting the behavior of any objects. -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Slow query in 8.2.0
Manuel Sugawara writes: > I have a query that use to work fine in 3 or 4 releases before 8.2 but > now it's very slow, same database same data set, explain analyze > follows: You've omitted all the interesting details --- obviously registro_trayectoria is a view, but of what? Please show the table and view definitions involved. 8.2 is a good bit more conservative than previous releases about optimizing views involving volatile functions, so I'm wondering whether that could have anything to do with it ... regards, tom lane ---(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
[GENERAL] Slow query in 8.2.0
Hi all,
I have a query that use to work fine in 3 or 4 releases before 8.2 but
now it's very slow, same database same data set, explain analyze
follows:
8.2.0
ciencias=# explain analyze SELECT registro_id FROM vregacd.registro_trayectoria
WHERE cuenta = '086521071' AND grupo_id = '160940';
QUERY PLAN
--
Nested Loop (cost=4033.17..9302.65 rows=1 width=4) (actual
time=11293.047..24123.221 rows=1 loops=1)
-> Hash Join (cost=4033.17..9300.99 rows=1 width=4) (actual
time=11293.002..24123.169 rows=1 loops=1)
Hash Cond: (t.alumno_id = a.id)
-> Seq Scan on trayectoria t (cost=0.00..4635.98 rows=42122
width=66) (actual time=21.003..14186.758 rows=42236 loops=1)
Filter: (causa_baja_id IS NULL)
-> Hash (cost=4033.16..4033.16 rows=1 width=4) (actual
time=9874.651..9874.651 rows=1 loops=1)
-> Hash Join (cost=8.02..4033.16 rows=1 width=4) (actual
time=5198.908..9874.643 rows=1 loops=1)
Hash Cond: (p.persona_id = a.persona_id)
-> Seq Scan on persona p (cost=0.00..3291.99 rows=48876
width=85) (actual time=34.254..9786.017 rows=48876 loops=1)
-> Hash (cost=8.02..8.02 rows=1 width=8) (actual
time=0.054..0.054 rows=1 loops=1)
-> Index Scan using "AlumnoNúmeroCuentaÚnico" on
alumno a (cost=0.00..8.02 rows=1 width=8) (actual time=0.039..0.044 rows=1
loops=1)
Index Cond: (cuenta = 86521071)
-> Index Scan using "RegistroAlumnoGrupoÚnico" on registro r
(cost=0.00..1.65 rows=1 width=8) (actual time=0.031..0.033 rows=1 loops=1)
Index Cond: ((t.trayectoria_id = r.trayectoria_id) AND (r.grupo_id =
160940))
Total runtime: 24123.953 ms
(15 filas)
8.1.4
ciencias=# explain analyze SELECT registro_id FROM vregacd.registro_trayectoria
WHERE cuenta = '086521071' AND grupo_id = '160940';
QUERY PLAN
--
Nested Loop (cost=0.00..21.25 rows=1 width=4) (actual time=0.747..0.747
rows=0 loops=1)
-> Nested Loop (cost=0.00..17.14 rows=1 width=8) (actual time=0.737..0.737
rows=0 loops=1)
-> Nested Loop (cost=0.00..11.20 rows=1 width=8) (actual
time=0.487..0.502 rows=1 loops=1)
-> Index Scan using "AlumnoNúmeroCuentaÚnico" on alumno a
(cost=0.00..5.47 rows=1 width=8) (actual time=0.248..0.252 rows=1 loops=1)
Index Cond: (cuenta = 86521071)
-> Index Scan using alumno_try_alumno on trayectoria t
(cost=0.00..5.72 rows=1 width=8) (actual time=0.220..0.227 rows=1 loops=1)
Index Cond: ("outer".id = t.alumno_id)
Filter: (causa_baja_id IS NULL)
-> Index Scan using "RegistroAlumnoGrupoÚnico" on registro r
(cost=0.00..5.93 rows=1 width=8) (actual time=0.223..0.223 rows=0 loops=1)
Index Cond: (("outer".id = r.trayectoria_id) AND (r.grupo_id =
160940))
-> Index Scan using persona_pkey on persona p (cost=0.00..4.10 rows=1
width=4) (never executed)
Index Cond: ("outer".persona_id = p.id)
Total runtime: 1.465 ms
(13 rows)
Any ideas?
Regards,
Manuel.
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
Re: [GENERAL] Marking indexes out of date (WAS: loading data,
Glen Parker wrote: > Martijn van Oosterhout wrote: > > On Fri, Dec 08, 2006 at 12:49:30PM -0800, Glen Parker wrote: > >> I'd like to see a general way to take indexes off line without actually > >> losing their definitions. For example, something like "ALTER TABLE [EN > >> | DIS] ABLE INDEXES", "ALTER INDEX [EN | DIS] ABLE", etc. This could > >> also be used internally when a backend encounters an error > >> reading/writing an index. Rather than refusing to execute queries, it > >> could just ignore indexes it knows are disabled or bad in some way and > >> re-plan as needed. > > > > One issue would be that even disabled indexes would need to be updated > > when there are new rows. If you don't update the index when it's > > disabled, then re-enabling will essentially need to rebuild the index. > > > That's what I had in mind. You could just as easily blow away the index > file(s). It's just that I don't want it to toss the index *definition*. Well, you can just pg_dump the index definition to a file. What more would someone want? -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] creating an index with tablespace on a dedicated filesystem (postgresql 8.1.5)
Vlad <[EMAIL PROTECTED]> writes: > we use tablespace feature to keep indexes on a dedicated raid array > (for performance). What I've noticed is that when the index were > created, based on the i/o activity it seemed that postgres was > creating the index file somewhere on the array where the default table > space is hosted and the data is stored. Temp files are created in $PGDATA/base/yourdb/pgsql_tmp/. If you have a mind to, you can replace that subdirectory with a symlink to a (suitably secure) directory elsewhere. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] creating an index with tablespace on a dedicated filesystem (postgresql 8.1.5)
Hello, we use tablespace feature to keep indexes on a dedicated raid array (for performance). What I've noticed is that when the index were created, based on the i/o activity it seemed that postgres was creating the index file somewhere on the array where the default table space is hosted and the data is stored. Then, when the index was completed, it moved index file to array for indexes. If my observation is accurate, and it really works this way, then it's definitely point for improvement imho... -- Vlad ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] INSERT INTO row value constructors
Cool. Now I just have to wait for Debian backports, or figure it out for myself if I can find the time. *sigh* 8.2 isn't even in Portage yet. -- Brandon Aiken CS/IT Systems Engineer -Original Message- From: Martijn van Oosterhout [mailto:[EMAIL PROTECTED] Sent: Wednesday, December 13, 2006 5:01 PM To: Brandon Aiken Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] INSERT INTO row value constructors On Wed, Dec 13, 2006 at 04:55:00PM -0500, Brandon Aiken wrote: > This is just an odd question. Previously RDBMSs (MySQL, SQLite, IIRC) I've used have allowed row value constructors (optional feature F641 in SQL'03) on INSERT statements. That is: > > INSERT INTO mytable > VALUES (0,'hello'),(1,'world'); > Is that correct, or is this behavior expected to change? It's in 8.2, see the documentation: http://www.postgresql.org/docs/current/static/dml-insert.html Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to litigate. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] a question for the way-back machine
Ben <[EMAIL PROTECTED]> writes: > Wouldn't it need to check the unique constraint (an index on the table) > before the insert can succeed? It seems like it would be better to check > the index than to do a full table scan to try to satisfy that constraint. Postgres contains no code that would even consider doing a seqscan for a uniqueness check; it could not work because of race-condition considerations, never mind performance. You have not given us any clue about where the seqscans are really coming from ... have you got any user-defined triggers, SQL functions in CHECK constraints, stuff like that? regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] pg_controldata output documentation
I'd like to calculate the name of the WAL file associated with the latest checkpoint using the output from pg_controldata: Latest checkpoint location: 74/FD9827C0 Prior checkpoint location:74/FD6FCDC8 Latest checkpoint's REDO location:74/FD87DDF8 Latest checkpoint's UNDO location:0/0 Latest checkpoint's TimeLineID: 1 Latest checkpoint's NextXID: 121153385 Latest checkpoint's NextOID: 133301 Latest checkpoint's NextMultiXactId: 1 Latest checkpoint's NextMultiOffset: 0 Is it as simple as 0001007400FD using Latest checkpoint's TimeLineID for the first 8 characters and Latest checkpoint location for the remaining 16 characters? I'm not running PG ver 8.2 so I don't have access to pg_xlogfile_name Many thanks ... Andy -- Andrew Rost National Operational Hydrologic Remote Sensing Center (NOHRSC) National Weather Service, NOAA 1735 Lake Dr. West, Chanhassen, MN 55317-8582 Voice: (952)361-6610 x 234 Fax: (952)361-6634 [EMAIL PROTECTED] http://www.nohrsc.noaa.gov ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] a question for the way-back machine
When you insert a tuple, it needs to be inserted into the index, yes. There is no way an insert can cause a sequential scan, except by some trigger defined on the table. Actually, as it happens, there *is* a trigger defined on the table to fire before insert, but it too uses an index scan, at least according to explain. Though, you'd think if it actually was using an index scan, that would be showing up in pg_stat_user_tables, which it isn't. Might the fact that the trigger is a plpgsql function be throwing it off and keeping it from using more recent planner stats? You're not doing a select within the insert statement are you? No, just within the trigger. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] plperl exception catching
On Wed, Dec 13, 2006 at 05:04:42PM -0500, Tom Lane wrote: > Bricklen Anderson <[EMAIL PROTECTED]> writes: > > Marc Evans wrote: > >> OK, I must be missing something obvious: > >> ERROR: creation of Perl function failed: 'eval "string"' trapped by > >> operation mask at line 2. > > > Try as plperlu > > This brings up the question of whether it'd be safe to allow eval in > plperl functions. I'm not sure why it's excluded now ... does it allow > access to untrusted operations? ISTM there being something about the Safe module in perl not being able to enable eval while staying "safe", so to speak. Looking at the safe module it looks like you can exclude certain functions from restrictions. The manpage has an example, so a simple try/catch mechanism could be created if enabling "eval" directly isn't ok. Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to > litigate. signature.asc Description: Digital signature
Re: [GENERAL] a question for the way-back machine
On Wed, Dec 13, 2006 at 02:01:46PM -0800, Ben wrote: > > > On Wed, 13 Dec 2006, Martijn van Oosterhout wrote: > > >>- 7.3 isn't smart enough to use an index on an insert? Seems unlikely. > > > >This question makes no sense, you don't need an index to insert. > > Wouldn't it need to check the unique constraint (an index on the table) > before the insert can succeed? It seems like it would be better to check > the index than to do a full table scan to try to satisfy that constraint. When you insert a tuple, it needs to be inserted into the index, yes. There is no way an insert can cause a sequential scan, except by some trigger defined on the table. > >Are you sure it's not due to some foreign key check? > > No, but it seems unlikely, given that the vast majority of activity is > inserts into a single table, and that this table has massive amounts of > sequential scans according to pg_stat_user_tables. You're not doing a select within the insert statement are you? Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to > litigate. signature.asc Description: Digital signature
Re: [GENERAL] plperl exception catching
Bricklen Anderson <[EMAIL PROTECTED]> writes: > Marc Evans wrote: >> OK, I must be missing something obvious: >> ERROR: creation of Perl function failed: 'eval "string"' trapped by >> operation mask at line 2. > Try as plperlu This brings up the question of whether it'd be safe to allow eval in plperl functions. I'm not sure why it's excluded now ... does it allow access to untrusted operations? regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] a question for the way-back machine
On Wed, 13 Dec 2006, Martijn van Oosterhout wrote: - 7.3 isn't smart enough to use an index on an insert? Seems unlikely. This question makes no sense, you don't need an index to insert. Wouldn't it need to check the unique constraint (an index on the table) before the insert can succeed? It seems like it would be better to check the index than to do a full table scan to try to satisfy that constraint. Are you sure it's not due to some foreign key check? No, but it seems unlikely, given that the vast majority of activity is inserts into a single table, and that this table has massive amounts of sequential scans according to pg_stat_user_tables. BTW, seperate inserts is the worst way to load data. At least put them within a single transaction, or use COPY. Oh, I know. It's not my choice, and not (yet) changeable. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] INSERT INTO row value constructors
On Wed, Dec 13, 2006 at 04:55:00PM -0500, Brandon Aiken wrote: > This is just an odd question. Previously RDBMSs (MySQL, SQLite, IIRC) I've > used have allowed row value constructors (optional feature F641 in SQL'03) on > INSERT statements. That is: > > INSERT INTO mytable > VALUES (0,'hello'),(1,'world'); > Is that correct, or is this behavior expected to change? It's in 8.2, see the documentation: http://www.postgresql.org/docs/current/static/dml-insert.html Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to > litigate. signature.asc Description: Digital signature
[GENERAL] INSERT INTO row value constructors
This is just an odd question. Previously RDBMSs (MySQL, SQLite, IIRC) I've used have allowed row value constructors (optional feature F641 in SQL'03) on INSERT statements. That is: INSERT INTO mytable VALUES (0,'hello'),(1,'world'); Is essentially shorthand for: INSERT INTO mytable VALUES (0,'hello'); INSERT INTO mytable VALUES (1,'world'); Under PostgreSQL, though, the parser thinks the row value constructors are arrays. This isn't a problem, per se, except to make me realize that PostgreSQL is unlikely to adopt F641. Is that correct, or is this behavior expected to change? This is just a curiosity question. I expect that PostgreSQL would behave in a similar manner performance wise inside a transaction. It just struck me as odd when it didn't work. -- Brandon Aiken CS/IT Systems Engineer Confidentiality Notice This email, including attachments, may include confidential and/or proprietary information, and may be used only by the person or entity to which it is addressed. If the reader of this email is not the intended recipient or his/her authorized agent, the reader is hereby notified that any dissemination, distribution or copying of this email is prohibited. If you have received this email in error, please notify the sender by replying to this message and deleting this email immediately. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] about the RULE system
On Wed, 2006-12-13 at 15:36, Rafal Pietrak wrote: > On Wed, 2006-12-13 at 14:01 -0500, Tom Lane wrote: > > Rafal Pietrak <[EMAIL PROTECTED]> writes: > > > I thought trigger functions execute at root/postgres security level? > > > > No. You probably want to make that function SECURITY DEFINER so it > > executes as the owner, but this isn't default for triggers. > > Hmmm. Have checked it, and it does not look promissing. > > Obviously, when I define function with "SECURITY DEFINER" I need to > limit access to that function. But > > "REVOKE ALL ON FUNCTION piti() FROM PUBLIC" > > Doe not seam to have any effect on functions installed as a trigger. Does your "common user" have the permission to create users? ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] about the RULE system
On Wed, 2006-12-13 at 14:01 -0500, Tom Lane wrote: > Rafal Pietrak <[EMAIL PROTECTED]> writes: > > I thought trigger functions execute at root/postgres security level? > > No. You probably want to make that function SECURITY DEFINER so it > executes as the owner, but this isn't default for triggers. Hmmm. Have checked it, and it does not look promissing. Obviously, when I define function with "SECURITY DEFINER" I need to limit access to that function. But "REVOKE ALL ON FUNCTION piti() FROM PUBLIC" Doe not seam to have any effect on functions installed as a trigger. I have just checked it, and my 'common user' is able to "INSERT INTO debi (id,name) VALUES (22, 'jklsdf')" after the above REVOKE. How do I limit access to such function? Even worse, my 'common user' was able to CREATE TEMPORARY TABLE, and install that function on that table, although "PUBLIC.SCHEMA" is available for USAGE only for those users. I generally accept 'common user' to have the ability to create temporary tables, but the takeover trigger function designed to other purposes AND DEFINED with "security definer" is not really acceptable. How can I limit that sort of missuse? -- -R ---(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] a question for the way-back machine
On Wed, Dec 13, 2006 at 01:10:44PM -0800, Ben wrote: > I'm using an ancient version of postgresql (7.3 - don't ask) and I'm > experiencing a problem where many inserts into an empty table slow down > over time, even if I analyze in the middle of the insertions. > pg_stat_user_tables shows lots and lots of full scans, which explains the > slowdown, but leads me to wonder why the full scans. Each insert is its > own transaction. This table has at least one unique index on it, and a > couple other multi-column indexes, so I was wondering if maybe that was > what was causing the full scans. But a an explain shows that a select for > that unique column will use the index, so now I'm wondering if maybe: > > - 7.3 isn't smart enough to use an index on an insert? Seems unlikely. This question makes no sense, you don't need an index to insert. Are you sure it's not due to some foreign key check? BTW, seperate inserts is the worst way to load data. At least put them within a single transaction, or use COPY. Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to > litigate. signature.asc Description: Digital signature
Re: [GENERAL] Large IN query optimization
On Wed, Dec 13, 2006 at 02:08:44PM -0500, Tom Lane wrote: > Are you on 8.2? 8.1 is pretty stupid about joins to inheritance trees. To put up a number for "stupid": For GNUmed a particular query involving one-level inheritance went down from over ten minutes to under one second just by running against 8.2 instead of below 8.2. Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] a question for the way-back machine
I'm using an ancient version of postgresql (7.3 - don't ask) and I'm experiencing a problem where many inserts into an empty table slow down over time, even if I analyze in the middle of the insertions. pg_stat_user_tables shows lots and lots of full scans, which explains the slowdown, but leads me to wonder why the full scans. Each insert is its own transaction. This table has at least one unique index on it, and a couple other multi-column indexes, so I was wondering if maybe that was what was causing the full scans. But a an explain shows that a select for that unique column will use the index, so now I'm wondering if maybe: - 7.3 isn't smart enough to use an index on an insert? Seems unlikely. - I have to start a new session to use new planner stats, even though each insert is in it's own transaction? Something I haven't verified for myself yet but have been told by others is that if I start these inserts quickly after installing a new database cluster (which probably translates as: before running "vacuum analyze" for the first time), then these slowdowns do not occur. I can't figure out why that might be. Any other thoughts? ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] grant all privileges on database
[EMAIL PROTECTED] wrote: grant all privileges on database test to auser As far as I can tell this does nothing. Intuitively this command suggests that the auser would be able to access and modify the database test in anyway. It would also suggest that as new tables for the database auser would automatically have access to them. It just grants all privileges for a database object to auser. You should check out the grant manager in Lightning Admin for Postgresql, it visually shows everything and makes it a bitter easier to see how it works. http://www.amsoftwaredesign.com/onlinehelp/pgla/grant_manager.htm In the screenshot the cells that are green are the ones that can be set for a particular object, and for a database it's just two. So the grant statement you used above did indeed do something, but only for a database object. Hope this helps. Later, -- Tony Caduto AM Software Design http://www.amsoftwaredesign.com Home of PG Lightning Admin for Postgresql Your best bet for Postgresql Administration Only $9.99 through January 1st 2007 ---(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] plperl exception catching
Marc Evans wrote:
OK, I must be missing something obvious:
c3i=> CREATE OR REPLACE FUNCTION foo_trigger() RETURNS TRIGGER AS $$
c3i$> eval { spi_exec_query('INSERT INTO FOO_BAR VALUES(1)'); } ||
c3i$> spi_exec_query('SELECT 1 as foo');
c3i$> $$ LANGUAGE plperl;
ERROR: creation of Perl function failed: 'eval "string"' trapped by
operation mask at line 2.
Try as plperlu
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [GENERAL] plperl exception catching
On Wed, 13 Dec 2006, Tom Lane wrote:
Marc Evans <[EMAIL PROTECTED]> writes:
Is it accurate for me to believe that there is no way to catch exception
within plperl?
You do it the same way you trap any other error in perl ...
OK, I must be missing something obvious:
c3i=> CREATE OR REPLACE FUNCTION foo_trigger() RETURNS TRIGGER AS $$
c3i$> eval { spi_exec_query('INSERT INTO FOO_BAR VALUES(1)'); } ||
c3i$> spi_exec_query('SELECT 1 as foo');
c3i$> $$ LANGUAGE plperl;
ERROR: creation of Perl function failed: 'eval "string"' trapped by
operation mask at line 2.
- Marc
---(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] MySQL drops support for most distributions
Joshua D. Drake wrote: > On Wed, 2006-12-13 at 13:20 -0500, John D. Burger wrote: >> Surely there are also third-party companies that provide "support" >> for MySqueal in some similar sense? Yeah. HP for example [links below]. HP announced support for Debian and MySQL (and the JBoss Stack as well). > Of course :) but... Fortune 2500+ for the most part will *not* use a > third party for support for something like MySQL. You've got to be kidding. Surely many Fortune 2500+ would prefer their MySQL support from HP than from a little company like MySQL-AB, wouldn't they? http://h20219.www2.hp.com/services/cache/442408-0-0-225-121.html http://h20219.www2.hp.com/services/cache/390925-0-0-0-121.html ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] grant all privileges on database
On Wed, Dec 13, 2006 at 12:20:03PM -0800, [EMAIL PROTECTED] wrote: > grant all privileges on database test to auser > > As far as I can tell this does nothing. Intuitively this command suggests > that the auser would be able to access and modify the database test in > anyway. It would also suggest that as new tables for the database auser > would automatically have access to them. It's not intuitive to me. Just like granting full access to the root of a filesystem does not grant you access to every file on it. Each directory and file needs to be done also. > Instead it appears that we have to still individually grant access to > tables on an individual basis. It seems to me that if it did pass these > blanket privileges on it would be very useful and make administration a > lot easier. While it is not hard to initially grant the individual access > (i am looking for a script) it is a pain in the butt to maintain. Is this > in fact how it works? I beleive the usual approach is you create a role and give permissions to tables to that role and then for each user that comes along, you assign that role to the user. That makes administration easier I think. Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to > litigate. signature.asc Description: Digital signature
[GENERAL] grant all privileges on database
grant all privileges on database test to auser As far as I can tell this does nothing. Intuitively this command suggests that the auser would be able to access and modify the database test in anyway. It would also suggest that as new tables for the database auser would automatically have access to them. Instead it appears that we have to still individually grant access to tables on an individual basis. It seems to me that if it did pass these blanket privileges on it would be very useful and make administration a lot easier. While it is not hard to initially grant the individual access (i am looking for a script) it is a pain in the butt to maintain. Is this in fact how it works? While I am sure someone will reply that by forcing us to individually grant access on a table by table basis you are inherently forcing the administrators to maintain high security - which is a good thing. However i would then reply that in fact because most db admins are lazy this means that they won't even bother and just use the super user privilege instead. ---(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] MySQL drops support for most distributions
Tom Lane wrote: The other point I'd make against John's argument is that there are a whole lot of Fortune 500 companies buying Red Hat support, and RH is effectively a third party for large chunks of Linux. (Of course, there are also large chunks for which Red Hat employees write as much code as anyone Yeah, I've heard that. :) I think the real criterion for big companies is not so much whether you're supporting your "own" product as whether you're big enough to be worth suing if things go wrong. I think you're right, and MySQL is unlikely to allow anybody else to get that big. - John Burger MITRE ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] selinux/postgresql/jfs
i'm working with an FC5 test server which i'm trying to get set up so we can benchmark xfs vs. jfs vs. ext3(writeback). does anyone have any suggestions on setting up a tablespace on a jfs partition with selinux in enforcing mode? (i'm likely going to turn selinux off for the moment, but it'd be interesting to compare numbers with/without selinux so we may attempt to do just that.) richard -- Richard Welty[EMAIL PROTECTED] 1-866-MY-CELERY 518-269-8232 (cell) ---(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] MySQL drops support for most distributions
Markus Schiltknecht <[EMAIL PROTECTED]> writes: > John D. Burger wrote: >> Sure, but they won't use PG either, for essentially the same reason, >> since =all= PG support is "third party". > So one can debate if i.e. EnterpriseDB is providing third party support > for PostgreSQL or first-hand support for their own product :-) The other point I'd make against John's argument is that there are a whole lot of Fortune 500 companies buying Red Hat support, and RH is effectively a third party for large chunks of Linux. (Of course, there are also large chunks for which Red Hat employees write as much code as anyone; but certainly that's not true for every package.) I think the real criterion for big companies is not so much whether you're supporting your "own" product as whether you're big enough to be worth suing if things go wrong. regards, tom lane ---(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] changing the permission of _lots_ of tables
Andreas, Richard, Thanks. The link below is like of what I was looking for if there wasn't an already written script. been through all three of my Pg books but didn't find any mention of this type of scripting. Rod -- A. Kretschmer wrote: am Wed, dem 13.12.2006, um 7:55:48 + mailte Richard Huxton folgendes: Roderick A. Anderson wrote: Works great ... up to a point. I now need to change the users and owners associated with all the tables without changing their permissions. Is there a way or hack to do mass changes like these. Write a small plpgsql function to take a pattern and set permissions based on that. A quick bit of googling should find some examples for you. Perhaps this one: http://people.planetpostgresql.org/greg/index.php?/archives/38-Scripting-with-psql.html Regards, Andreas ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] plperl exception catching
Marc Evans <[EMAIL PROTECTED]> writes: > Is it accurate for me to believe that there is no way to catch exception > within plperl? You do it the same way you trap any other error in perl ... regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Large IN query optimization
"Worky Workerson" <[EMAIL PROTECTED]> writes: > I'm wondering (given what I have read about PG and IN), if there is a > better way to execute this query. > ... (as I have partitioned the events table by ip): Are you on 8.2? 8.1 is pretty stupid about joins to inheritance trees. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] MySQL drops support for most distributions
On Wed, 2006-12-13 at 13:37 -0500, Bill Moran wrote: > In response to "John D. Burger" <[EMAIL PROTECTED]>: > > > > The good thing is that there are several companies supporting > > > Postgres, > > > so whatever one of them does it does not affect the market as a whole. > > > > Surely there are also third-party companies that provide "support" > > for MySqueal in some similar sense? > > Couple of years ago when I was part owner of a company, we tried to > become an "official" MySQL support provider. > > Now, this is a three man operation, we had about 10 clients and were > looking to expand into the MySQL space. > > We found the money MySQL wanted to become "official" to be excessive. > Additionally, for that money, we didn't get promised anything -- we > couldn't even get an estimate of how many potential clients there > would be in our area. After much discussion with the MySQL people, > we finally decided it was too much money to take the risk. > > I wonder how many other potential support companies felt the same > way? Perhaps that was a bad business decision on our part, but we'll > never know now -- we shut the company down a year ago. What you describe above is a very similar thing that brought CMD (as its current incarnation) into being. We tried to get tier 4 support from a little known company called Great Bridge years ago The basic idea was that we would call them maybe 4 times a year but wanted to work with them because they had the "name" for PostgreSQL. They wanted 16k a year. Now they are dust, and CMD is what it is today ;) Sincerely, Joshua D. Drake -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] MySQL drops support for most distributions
Hi, John D. Burger wrote: Sure, but they won't use PG either, for essentially the same reason, since =all= PG support is "third party". Maybe. But at least these third parties can take the source and build their own product on top of it, without significant limitations. So one can debate if i.e. EnterpriseDB is providing third party support for PostgreSQL or first-hand support for their own product :-) Regards Markus ---(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] MySQL drops support for most distributions
On Wed, 2006-12-13 at 13:00 -0600, Tony Caduto wrote: > John D. Burger wrote: > > > > Sure, but they won't use PG either, for essentially the same reason, > > since =all= PG support is "third party". > > > > > They would probably use EnterpriseDB though :-) Or Command Prompt like several extremely large companies already do ;) Joshua D. Drake > -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Large Object to Bytea Conversion
Hi, I'm using the function below to convert a large object to a bytea value. Unfortunately, it performs very poorly for *large* objects, i.e. I'm currently fiddling with a file of about 100 MB. I've increased work_mem to 256 MB, but that didn't help much. I suspect the aggregate function is not as efficient as it looks. Is it probably not releasing memory? The postgres process consumes all the work_mem I've allowed. Theoretically, this could be a streaming operation and would not need much memory at all. As bytea is normally a varlena field, I suspect postgres needs to be able to hold the file at least once in memory. Any idea on how to speed this up? Regards Markus Dimitri Fontaine wrote: Hi, Le mardi 14 novembre 2006 14:36, Markus Schiltknecht a écrit : I want to convert some large objects to bytea fields on the server. Searching through the documentation didn't reveal any hints. Am I missing something or is there really no such thing as a lo_convert_to_bytea function? You may want to try this code given on IRC by i-can't-remember-who: CREATE FUNCTION _phpads_agg_concat (bytea, bytea) RETURNS bytea AS $_$SELECT $1 || $2$_$ LANGUAGE sql IMMUTABLE STRICT; CREATE AGGREGATE phpads_agg_concat (bytea) (SFUNC = _phpads_agg_concat, STYPE = bytea, INITCOND = ''); EXPLAIN ANALYZE SELECT phpads_agg_concat(data) FROM (SELECT data FROM pg_largeobject WHERE loid = 24513361 ORDEY BY pageno) t; QUERY PLAN --- Aggregate (cost=10.62..10.63 rows=1 width=32) (actual time=4.682..4.685 rows=1 loops=1) -> Index Scan using pg_largeobject_loid_pn_index on pg_largeobject (cost=0.00..10.57 rows=4 width=1863) (actual time=0.075..0.162 rows=4 loops=1) Index Cond: (loid = 24513361::oid) Total runtime: 5.146 ms CREATE FUNCTION phppgads_lo_readall(oid) RETURNS bytea AS $_$SELECT phpads_agg_concat(data) FROM (SELECT data FROM pg_largeobject WHERE loid = $1 ORDEY BY pageno) t$_$ LANGUAGE sql STRICT; Hope this will help, ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] about the RULE system
Rafal Pietrak <[EMAIL PROTECTED]> writes: > I thought trigger functions execute at root/postgres security level? No. You probably want to make that function SECURITY DEFINER so it executes as the owner, but this isn't default for triggers. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] MySQL drops support for most distributions
John D. Burger wrote: Sure, but they won't use PG either, for essentially the same reason, since =all= PG support is "third party". They would probably use EnterpriseDB though :-) -- Tony Caduto AM Software Design http://www.amsoftwaredesign.com Home of PG Lightning Admin for Postgresql Your best bet for Postgresql Administration ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] EXECUTE INTO on 8.2
"Jon Asher" <[EMAIL PROTECTED]> writes: > I'm seeing some strange behavior with the following code. It compiles and > runs but returns an error on the Execute statement: > List index out of bounds(0) Worksforme. Want to provide a complete example instead of a fragmentary one? regression$# create or replace function foo() returns int as $$ declare srec record; v_formula varchar; v_result varchar; BEGIN v_formula = 'select 4 as val'; EXECUTE v_formula INTO srec; return srec.val; end $$ language plpgsql; CREATE FUNCTION regression=# select foo(); foo - 4 (1 row) regression=# regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] plperl exception catching
Hi - Is it accurate for me to believe that there is no way to catch exception within plperl? For example, if an update fails, I'd like to do an insert. Yes, this is trivial to do in plpgsql, but sadly plpgsql is not usable due to not supporting variable variables. If plperl can't catch exceptions, is there a language that can be used? Thanks in advance - Marc ---(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] MySQL drops support for most distributions
Joshua D. Drake wrote: Surely there are also third-party companies that provide "support" for MySqueal in some similar sense? Of course :) but... Fortune 2500+ for the most part will *not* use a third party for support for something like MySQL. Sure, but they won't use PG either, for essentially the same reason, since =all= PG support is "third party". - John Burger MITRE ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] MySQL drops support for most distributions
In response to "John D. Burger" <[EMAIL PROTECTED]>: > > The good thing is that there are several companies supporting > > Postgres, > > so whatever one of them does it does not affect the market as a whole. > > Surely there are also third-party companies that provide "support" > for MySqueal in some similar sense? Couple of years ago when I was part owner of a company, we tried to become an "official" MySQL support provider. Now, this is a three man operation, we had about 10 clients and were looking to expand into the MySQL space. We found the money MySQL wanted to become "official" to be excessive. Additionally, for that money, we didn't get promised anything -- we couldn't even get an estimate of how many potential clients there would be in our area. After much discussion with the MySQL people, we finally decided it was too much money to take the risk. I wonder how many other potential support companies felt the same way? Perhaps that was a bad business decision on our part, but we'll never know now -- we shut the company down a year ago. Anyway, I guess my point is that it was a whole lot easier to get listed as a company supporting PostgreSQL than it was MySQL. We were listed on the commercial support part of the site the entire time we were in business -- got at least one client from it. I don't think we did any MySQL support the whole time we were in business. Perhaps big companies with lotsa money wouldn't find MySQL's offerings to be a bad deal, but we couldn't justify it and I suspect a lot of small companies can't. Anyway, now I do PostgreSQL work for Collaborative Fusion and I'm much happier because it's not my job to worry about those kind of business relationship decisions -- there are competent people handling that. -- Bill Moran Collaborative Fusion Inc. [EMAIL PROTECTED] Phone: 412-422-3463x4023 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] EXECUTE INTO on 8.2
I'm seeing some strange behavior with the following code. It compiles and runs but returns an error on the Execute statement: List index out of bounds(0) DECLARE srec record; v_formula varchar; v_result varchar; BEGIN v_formula = 'select 4 as val'; EXECUTE v_formula INTO srec; END; However, the old school version runs w/no problem: BEGIN v_formula = 'select 4 as val'; FOR srec IN EXECUTE v_formula LOOP v_result = srec.val; END LOOP; END; Any idea why the new syntax isn't working? (I'm running 8.2 on a Windows dev box.)
Re: [GENERAL] Pltcl error - could not create "normal" interpreter
"Lenorovitz, Joel" <[EMAIL PROTECTED]> writes: > Could somebody shed any light on the error message below that came from > trying to call a simple pltcl test function? I am running Postgres 8.1 > on WinXP and just recently added the pltcl language by copying Tcl84.dll > into my system directory (C:/Windows/System32) and successfully issuing > the command: >> createlang pltcl -U db_admin postgres I was able to reproduce the "could not create "normal" interpreter" behavior after removing "init.tcl" from the Tcl library directory. Unfortunately, it seems that this breaks Tcl badly enough that it's not capable of returning a useful error message :-( --- the fact that our code doesn't make any attempt to print the interpreter result is probably Not Good, but when I changed it to do so, I still didn't get anything but an empty string ... So the short answer is "install Tcl per its directions". regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] MySQL drops support for most distributions
On Wed, 2006-12-13 at 13:20 -0500, John D. Burger wrote: > > The good thing is that there are several companies supporting > > Postgres, > > so whatever one of them does it does not affect the market as a whole. > > Surely there are also third-party companies that provide "support" > for MySqueal in some similar sense? Of course :) but... Fortune 2500+ for the most part will *not* use a third party for support for something like MySQL. MySQL is making a pretty bold statement here. They are saying, for business, and we mean business, we support RH and Suse which are *the* business Linux platforms. It really isn't that different that was most other commercial entities do. Sincerely, Joshua D. Drake > > - John Burger >MITRE > > > ---(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 > -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] MySQL drops support for most distributions
* John D. Burger ([EMAIL PROTECTED]) wrote: > >The good thing is that there are several companies supporting > >Postgres, > >so whatever one of them does it does not affect the market as a whole. > > Surely there are also third-party companies that provide "support" > for MySqueal in some similar sense? This is, truely, a very interesting question. I'm not 100% sure about this but I thought that the non-GPL version of MySQL was tied in with their support contracts. If this is the case (and I could be wrong) there's no option to go elsewhere for support if you're using the non-GPL license (required if you don't want to give out your source code to anything which touches MySQL, or at least that's my understanding of how they interpret the 'derivative' concept in the GPL). So, there may be third-party companies which provide support for the GPL'd version of MySQL, but alot of people use the non-GPL version because they don't want to be bound by the GPL to release their source code. I'd be very curious if MySQL has an official say on this.. Of course, they could switch to PostgreSQL as it uses the BSD license... :) Thanks, Stephen signature.asc Description: Digital signature
Re: [GENERAL] MySQL drops support for most distributions
John D. Burger wrote: The good thing is that there are several companies supporting Postgres, so whatever one of them does it does not affect the market as a whole. Surely there are also third-party companies that provide "support" for MySqueal in some similar sense? There probably are, but one of the major selling points of MySQL to corporate types is 'official' support from the 'offical' company. -- Russ. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] Large IN query optimization
I'm wondering (given what I have read about PG and IN), if there is a
better way to execute this query. I have (essentially) two tables in
a read-only DB, ip_info and events, where many events can map to one
ip_info. I'd like to get all the ip_info.info columns where a
specific event occurred. This is about what I have come up with (with
indexes on all the columns):
CREATE TABLE ip_info ( ip IP4, info VARCHAR );
CREATE TABLE events ( ip IP4, event_name VARCHAR, event_type VARCHAR);
SELECT ip, info
FROM ip_info
WHERE ip IN (SELECT ip FROM events WHERE event_name = 'somename');
This works fine when there are few events named 'somename', however
this subquery can return a fairly large set of rows (>1 million) and
this query takes quite long to execute. Is there a better way to
write this query? What would be the optimal plan for this query,
given a roughly even distribution of event_name? My current plan
looks something like (as I have partitioned the events table by ip):
Hash Join
Hash Cond ("outer".ip = "inner".ip)
-> Seq Scan on ip_info
-> Hash
-> HashAggregate
-> Append
-> Index Scan using "events_ip_01_event_name_idx" on events_ip_01 events
Index Cond ...
-> Index Scan using "events_ip_02_event_name_idx" on events_ip_02 events
Index Cond ...
Is this the optimal plan for this query? BTW, ip_info has about 5M
rows, and the collective events tables have about 50M rows.
Also, slightly off-topic ... are there any optimizations that I can
use to tell PG that this is a read-only database?
PG 8.1.3 on RHEL4.3 x86_64 ... thinking about upgrading to 8.2 when I
get the time.
Thanks!
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [GENERAL] MySQL drops support for most distributions
The good thing is that there are several companies supporting Postgres, so whatever one of them does it does not affect the market as a whole. Surely there are also third-party companies that provide "support" for MySqueal in some similar sense? - John Burger MITRE ---(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] MySQL drops support for most distributions
On Wed, 2006-12-13 at 12:01, Alvaro Herrera wrote: > Scott Marlowe wrote: > > On Wed, 2006-12-13 at 10:50, David Goodenough wrote: > > > http://developers.slashdot.org/article.pl?sid=06/12/13/1515217&from=rss > > > > > > "MySQL quietly deprecated support for most Linux distributions on October > > > 16, > > > when its 'MySQL Network' support plan was replaced by 'MySQL Enterprise.' > > > MySQL now supports only two Linux distributions — Red Hat Enterprise > > > Linux > > > and SUSE Linux Enterprise Server. We learned of this when MySQL declined > > > to > > > sell us support for some new Debian-based servers. Our sales rep 'found > > > out > > > from engineering that the current Enterprise offering is no longer > > > supported > > > on Debian OS.' We were told that 'Generic Linux' in MySQL's list of > > > supported > > > platforms means 'generic versions of the implementations listed above'; > > > not > > > support for Linux in general." > > > > So, in a similar vein, which PostgreSQL support companies support > > Debian, for instance? > > I bet Credativ does. > > The good thing is that there are several companies supporting Postgres, > so whatever one of them does it does not affect the market as a whole. I was kinda thinking the same thing. Man, must suck to be tied to the one true company for your database when they stop supporting your OS etc... And what about MySQL windows flavor? ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] MySQL drops support for most distributions
On Wed, 2006-12-13 at 15:01 -0300, Alvaro Herrera wrote: > Scott Marlowe wrote: > > On Wed, 2006-12-13 at 10:50, David Goodenough wrote: > > > http://developers.slashdot.org/article.pl?sid=06/12/13/1515217&from=rss > > > > > > "MySQL quietly deprecated support for most Linux distributions on October > > > 16, > > > when its 'MySQL Network' support plan was replaced by 'MySQL Enterprise.' > > > MySQL now supports only two Linux distributions — Red Hat Enterprise > > > Linux > > > and SUSE Linux Enterprise Server. We learned of this when MySQL declined > > > to > > > sell us support for some new Debian-based servers. Our sales rep 'found > > > out > > > from engineering that the current Enterprise offering is no longer > > > supported > > > on Debian OS.' We were told that 'Generic Linux' in MySQL's list of > > > supported > > > platforms means 'generic versions of the implementations listed above'; > > > not > > > support for Linux in general." > > > > So, in a similar vein, which PostgreSQL support companies support > > Debian, for instance? > > I bet Credativ does. Command Prompt supports PostgreSQL on the following platforms: Full Support: Debian/Ubuntu, RH/FC, SuSE FreeBSD (Stable releases only) Win32 Solaris PostgreSQL only support (meaning how to configure the OS is up to you): Any Linux not listed above, e.g; Slackware, Mandriva etc... Sincerely, Joshua D. Drake > > The good thing is that there are several companies supporting Postgres, > so whatever one of them does it does not affect the market as a whole. > > ---(end of broadcast)--- > TIP 6: explain analyze is your friend > -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] MySQL drops support for most distributions
Scott Marlowe wrote: > On Wed, 2006-12-13 at 10:50, David Goodenough wrote: > > http://developers.slashdot.org/article.pl?sid=06/12/13/1515217&from=rss > > > > "MySQL quietly deprecated support for most Linux distributions on October > > 16, > > when its 'MySQL Network' support plan was replaced by 'MySQL Enterprise.' > > MySQL now supports only two Linux distributions — Red Hat Enterprise Linux > > and SUSE Linux Enterprise Server. We learned of this when MySQL declined to > > sell us support for some new Debian-based servers. Our sales rep 'found out > > from engineering that the current Enterprise offering is no longer > > supported > > on Debian OS.' We were told that 'Generic Linux' in MySQL's list of > > supported > > platforms means 'generic versions of the implementations listed above'; not > > support for Linux in general." > > So, in a similar vein, which PostgreSQL support companies support > Debian, for instance? I bet Credativ does. The good thing is that there are several companies supporting Postgres, so whatever one of them does it does not affect the market as a whole. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] dynamic plpgsql question
Marc Evans wrote:
On Wed, 13 Dec 2006, Erik Jones wrote:
Marc Evans wrote:
On Wed, 13 Dec 2006, Erik Jones wrote:
Marc Evans wrote:
Hi -
I am struggling with a trigger function in plpgsql, and am hoping
that someone on this list can't show me a way to do what I need.
In the trigger, TG_ARGV[0] is the name of a column that I want to
evaluate. This code shows the concept, though is not functional:
CREATE OR REPLACE FUNCTION foo() RETURNS TRIGGER AS $$
DECLARE
column_name TEXT := TG_ARGV[0];
data TEXT;
BEGIN
EXECUTE 'SELECT NEW.' || column_name INTO data;
-- ...
END;
$$ LANGUAGE plpgsql;
When I try to use that code, I receive:
c3i=> insert into test_table values (1,1);
ERROR: NEW used in query that is not in a rule
CONTEXT: SQL statement "SELECT NEW.magic"
How can I get the value of NEW.{column_name} (aka NEW.magic in
this specific test case) into the variable data?
EXECUTE 'SELECT ' || NEW.column_name ';' INTO data;
Thanks for the suggestion. Unfortunately, it does not work:
CREATE OR REPLACE FUNCTION foo() RETURNS TRIGGER AS $$
DECLARE
column_name TEXT := TG_ARGV[0];
data TEXT;
BEGIN
EXECUTE 'SELECT ' || NEW.column_name || ';' INTO date;
-- ...
END;
$$ LANGUAGE plpgsql;
c3i=> insert into test_table values (1,1);
ERROR: record "new" has no field "column_name"
Ah, sorry, I'd just arrived at work and wasn't quite away as of yet.
AFAIK, plpgsql doesn't have any facilities for variable substitution
in variable names (called variable variables in some languages).
However, if plpgsql is your only procedural option (plperl, I've
heard, does support this feature) and the possible values for column
name are known to you, there is a hackish workaround:
IF(column_name = 'foo') THEN
EXECUTE 'SELECT ' || NEW.foo || ';' INTO data;
ELSIF(column_name = 'bar') THEN
EXECUTE 'SELECT ' || NEW.bar || ';' INTO data;
ELSIF
.
.
.
You get the picture...
Thanks for the suggestion. I would be quiet content to use plperl, if
I could figure out a way to do the equivilant of plpgsql's:
EXECUTE 'INSERT INTO ' || table_name || ' VALUES(NEW.*)';
I suppsoe that in plperl I could walk the list of keys in $_TD->{new}
building a list of columns and values that are then placed in a
spi_prepare. Would that be the recommended technique?
- Marc
Sure, that'll work. Although, I'll admit, that with plperl I don't have
much experience so, if there's a better way of doing that, someone else
might know. Also, for a straight insert like that I don't really see
the need for using spi_prepare. Just feed the INSERT query string to
spi_exec_query.
--
erik jones <[EMAIL PROTECTED]>
software development
emma(r)
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
Re: [GENERAL] MySQL drops support for most distributions
On Wed, 2006-12-13 at 10:50, David Goodenough wrote: > http://developers.slashdot.org/article.pl?sid=06/12/13/1515217&from=rss > > "MySQL quietly deprecated support for most Linux distributions on October 16, > when its 'MySQL Network' support plan was replaced by 'MySQL Enterprise.' > MySQL now supports only two Linux distributions — Red Hat Enterprise Linux > and SUSE Linux Enterprise Server. We learned of this when MySQL declined to > sell us support for some new Debian-based servers. Our sales rep 'found out > from engineering that the current Enterprise offering is no longer supported > on Debian OS.' We were told that 'Generic Linux' in MySQL's list of supported > platforms means 'generic versions of the implementations listed above'; not > support for Linux in general." So, in a similar vein, which PostgreSQL support companies support Debian, for instance? ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] function accepting and returning rows; how to avoid
Merlin Moncure wrote:
On 12/13/06, Kevin Murphy <[EMAIL PROTECTED]> wrote:
I'd like to have a function that acts as a row filter (that can
optionally expand each row into multiple rows), but I don't know how to
wangle this such that the output is not enclosed in parentheses, i.e.
what I'm getting now is a single column of a composite type instead of
multiple columns matching the original table layout.
functions defined in the sql language (as opposed to pl/pgsql) allow
you to call them without explicitly using from...if you want to
expand, just select from your result as you would expand any row
variable. basically, have you tried:
select (explode(sometable.*)).* from sometable;
Thanks a lot. You must have been puzzled that I got so close and failed
to figure it out. I hadn't really understood the {row}.* notation. In
the meantime I did an end run with an insert trigger, but at least I
understand this now.
-Kevin
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
[GENERAL] about the RULE system
Hi All, This is something that bugs me for some time now. I have: (as user postgres I do) CREATE TABLE debi (id int, name text); REVOKE ALL ON debi FROM public; CREATE FUNCTION piti() RETURNS "trigger" AS $$ DECLARE me RECORD; BEGIN select * into me FROM pg_authid; new.id := me.oid; new.name := me.rolname; return new; END $$ LANGUAGE plpgsql; INSERT INTO debi (id,name) VALUES (22, 'jklsdf'); INSERT 0 1 INSERT INTO debi (id,name) VALUES (22, 'jklsdf'); INSERT 0 1 CREATE VIEW mdebi as SELECT * from debi; GRANT SELECT, insert on mdebi to public; (now I become common user) SELECT * from debi; ERROR: permission denied for relation debi SELECT * from mdebi; id | name +-- 10 | postgres 10 | postgres (2 rows) So far so good. But the insert fails: INSERT INTO mdebi (id,name) VALUES (22, 'jklsdf'); ERROR: permission denied for relation pg_authid CONTEXT: SQL statement "SELECT * from pg_authid" PL/pgSQL function "piti" line 1 at select into variables So it looks like the VIEW have elevated my security level thus allowing me to access the DEBI table (SELECT statement), but inside of the TRIGGER within DEBI I'm back with my original security level. This is weird. I thought trigger functions execute at root/postgres security level? But definitely I though, once I've passed VIEW access control, I'm prity mutch root/postgres superuser. Apparently not so. Why I can "SELECT * FROM pg_authid" within SELECT, and I cannot do that within INSERT (to the same table/view) is a real mistery to me. But, is there a way around it? (meaning: to have a trigger function do it's security related job on a table *not* publically accesable, but available for public access only through a specially designed VIEW). One thing though. I *realy* *really* *need* to do the job using trigger functions. Functions called from within the RULE-set are not an option here - although I wouldn't like to elaborate. Thenx -- -R ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Weird PG 8.1.3 date error
Tom Lane wrote: He needs to set DateStyle to match the expected field order. regression=# show datestyle; DateStyle --- ISO, MDY (1 row) regression=# select '08-01-2006'::date; date 2006-08-01 (1 row) regression=# set datestyle TO ymd; SET regression=# select '08-01-2006'::date; ERROR: date/time field value out of range: "08-01-2006" HINT: Perhaps you need a different "datestyle" setting. regression=# Tom, You were right on, that's exactly what the problem was. I have not heard back, but I told him to set his datestyle back to the default. Thanks, -- Tony ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Weird PG 8.1.3 date error
On Wed, 2006-12-13 at 10:37 -0600, Tony Caduto wrote: > Hi, > I have a user who is getting this error on a 8.1.3 server when importing > some data from a dbf file: > > PostgreSQL Error Code: (1) > ERROR: date/time field value out of range: "08-01-2006" > > He says the db is SQL-ASCII and the datestyle is at the default. Without more information it is a bit tough to say but: http://people.planetpostgresql.org/joshua/index.php?/archives/12-Ahhh!-PostgreSQL-Gotchas.html > > Of course on all my test servers the insert works perfectly without the > range error. > > Anyone have any Ideas? I don't know if the pg server is win32 or Unix. > > Thanks, > -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] storage
2006/12/13, Antonios Katsikadamos <[EMAIL PROTECTED]>: I would like to know where postgres stores the created database and the tables? http://www.postgresql.org/docs/current/static/storage-file-layout.html F. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Pltcl error - could not create "normal" interpreter
"Lenorovitz, Joel" <[EMAIL PROTECTED]> writes: > Could somebody shed any light on the error message below that came from > trying to call a simple pltcl test function? I am running Postgres 8.1 > on WinXP and just recently added the pltcl language by copying Tcl84.dll > into my system directory (C:/Windows/System32) and successfully issuing > the command: >> createlang pltcl -U db_admin postgres Hm, surely that .dll is not the only component of a standard Tcl installation? The error message is pretty unhelpful I agree, but I suspect it may mean that Tcl's attempt to execute some kind of init file failed. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] storage
Hi all, I would like to know where postgres stores the created database and the tables?Can anyone help? thnx for nay help, Antonios - Access over 1 million songs - Yahoo! Music Unlimited.
Re: [GENERAL] error messages without schema name
That means you might have the same table in both schemas, so try doing the following to confirm: select * from schema1.bar select * from schema2.bar You can also set search_path if you want to avoid adding schema name with database objects: set search_path = schema1 select * from bar instead of doing: select * from schema1.bar --- Shoaib Mir EnterpriseDB (www.enterprisedb.com) On 12/13/06, Filip Rembiałkowski <[EMAIL PROTECTED]> wrote: 06-12-13, Shoaib Mir <[EMAIL PROTECTED]> napisał(a): > You can not truncate table 'foo' because there is referential integrity > between 'foo' and 'bar', so try using > > TRUNCATE nsp2.foo CASCADE; > > You can find the schema name for 'bar' using a query like: > > select nspname from pg_namespace where oid = (select relnamespace from > pg_class where relname = 'bar'); this returns 2 schema names. which one is of my "bar"? Actually, I was asking if this behaviour of postgres is OK? I heard from my PostgreSQL teacher, that all such messages should be corrected in the source code, to include schema name too.
Re: [GENERAL] A VIEW mimicing a TABLE
On Wed, 2006-12-13 at 11:08 -0500, Tom Lane wrote: > > You can add a default to a view's column, either the same as the > underlying table's default, or different if you want. > > ALTER TABLE view ALTER COLUMN col SET DEFAULT expr G! The obvious solutions are most difficult to spot. Thenx! -- -R ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] MySQL drops support for most distributions
David Goodenough wrote: http://developers.slashdot.org/article.pl?sid=06/12/13/1515217&from=rss "MySQL quietly deprecated support for most Linux distributions on October 16, when its 'MySQL Network' support plan was replaced by 'MySQL Enterprise.' MySQL now supports only two Linux distributions — Red Hat Enterprise Linux and SUSE Linux Enterprise Server. We learned of this when MySQL declined to sell us support for some new Debian-based servers. Our sales rep 'found out from engineering that the current Enterprise offering is no longer supported on Debian OS.' We were told that 'Generic Linux' in MySQL's list of supported platforms means 'generic versions of the implementations listed above'; not support for Linux in general." I *really* hope this helps convince people to migrate to PostgreSQL. Every time I need to support MySQL I go that much more gray. :/ This could be good. Madi ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Weird PG 8.1.3 date error
Tony Caduto <[EMAIL PROTECTED]> writes: > I have a user who is getting this error on a 8.1.3 server when importing > some data from a dbf file: > ERROR: date/time field value out of range: "08-01-2006" He needs to set DateStyle to match the expected field order. regression=# show datestyle; DateStyle --- ISO, MDY (1 row) regression=# select '08-01-2006'::date; date 2006-08-01 (1 row) regression=# set datestyle TO ymd; SET regression=# select '08-01-2006'::date; ERROR: date/time field value out of range: "08-01-2006" HINT: Perhaps you need a different "datestyle" setting. regression=# He might also want to use tools that don't suppress the HINT part of an error message ... regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] dynamic plpgsql question
On Wed, 13 Dec 2006, Erik Jones wrote:
Marc Evans wrote:
On Wed, 13 Dec 2006, Erik Jones wrote:
Marc Evans wrote:
Hi -
I am struggling with a trigger function in plpgsql, and am hoping that
someone on this list can't show me a way to do what I need.
In the trigger, TG_ARGV[0] is the name of a column that I want to
evaluate. This code shows the concept, though is not functional:
CREATE OR REPLACE FUNCTION foo() RETURNS TRIGGER AS $$
DECLARE
column_name TEXT := TG_ARGV[0];
data TEXT;
BEGIN
EXECUTE 'SELECT NEW.' || column_name INTO data;
-- ...
END;
$$ LANGUAGE plpgsql;
When I try to use that code, I receive:
c3i=> insert into test_table values (1,1);
ERROR: NEW used in query that is not in a rule
CONTEXT: SQL statement "SELECT NEW.magic"
How can I get the value of NEW.{column_name} (aka NEW.magic in this
specific test case) into the variable data?
EXECUTE 'SELECT ' || NEW.column_name ';' INTO data;
Thanks for the suggestion. Unfortunately, it does not work:
CREATE OR REPLACE FUNCTION foo() RETURNS TRIGGER AS $$
DECLARE
column_name TEXT := TG_ARGV[0];
data TEXT;
BEGIN
EXECUTE 'SELECT ' || NEW.column_name || ';' INTO date;
-- ...
END;
$$ LANGUAGE plpgsql;
c3i=> insert into test_table values (1,1);
ERROR: record "new" has no field "column_name"
Ah, sorry, I'd just arrived at work and wasn't quite away as of yet. AFAIK,
plpgsql doesn't have any facilities for variable substitution in variable
names (called variable variables in some languages). However, if plpgsql is
your only procedural option (plperl, I've heard, does support this feature)
and the possible values for column name are known to you, there is a hackish
workaround:
IF(column_name = 'foo') THEN
EXECUTE 'SELECT ' || NEW.foo || ';' INTO data;
ELSIF(column_name = 'bar') THEN
EXECUTE 'SELECT ' || NEW.bar || ';' INTO data;
ELSIF
.
.
.
You get the picture...
Thanks for the suggestion. I would be quiet content to use plperl, if I
could figure out a way to do the equivilant of plpgsql's:
EXECUTE 'INSERT INTO ' || table_name || ' VALUES(NEW.*)';
I suppsoe that in plperl I could walk the list of keys in $_TD->{new}
building a list of columns and values that are then placed in a
spi_prepare. Would that be the recommended technique?
- Marc
---(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] dynamic plpgsql question
Marc Evans <[EMAIL PROTECTED]> writes: > In the trigger, TG_ARGV[0] is the name of a column that I want to > evaluate. This is effectively impossible in plpgsql, because it's a statically typed language --- it wants to know the type of every expression in advance, and so such a thing couldn't work. Consider using one of the other PLs instead. regards, tom lane ---(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
[GENERAL] MySQL drops support for most distributions
http://developers.slashdot.org/article.pl?sid=06/12/13/1515217&from=rss "MySQL quietly deprecated support for most Linux distributions on October 16, when its 'MySQL Network' support plan was replaced by 'MySQL Enterprise.' MySQL now supports only two Linux distributions — Red Hat Enterprise Linux and SUSE Linux Enterprise Server. We learned of this when MySQL declined to sell us support for some new Debian-based servers. Our sales rep 'found out from engineering that the current Enterprise offering is no longer supported on Debian OS.' We were told that 'Generic Linux' in MySQL's list of supported platforms means 'generic versions of the implementations listed above'; not support for Linux in general." ---(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
[GENERAL] Pltcl error - could not create "normal" interpreter
Greetings, Could somebody shed any light on the error message below that came from trying to call a simple pltcl test function? I am running Postgres 8.1 on WinXP and just recently added the pltcl language by copying Tcl84.dll into my system directory (C:/Windows/System32) and successfully issuing the command: > createlang pltcl -U db_admin postgres What else do I need to do or configure to enable this full functionality? postgres=# create or replace function test_fxn() returns void as $$ postgres$# spi_exec "SELECT * FROM test" postgres$# $$ language pltcl; CREATE FUNCTION postgres=# select test_fxn(); ERROR: could not create "normal" interpreter postgres=# Any help is greatly appreciated and the little that's in the archives/www is fairly abstruse. Thanks, JL ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] [EMAIL PROTECTED]: Re: port fault on pg_ctl's
--On onsdag, december 13, 2006 11.04.54 -0500 Tom Lane <[EMAIL PROTECTED]> wrote: Gergely CZUCZY <[EMAIL PROTECTED]> writes: This is wierd, I agree. The split between server and client is really made = in the postgresql sources, I just follow their guidelines. [ checks RPM specfiles ... ] Both Devrim and I put pg_ctl in the server subpackage, dunno where you think that the sources encourage something else ... regards, tom lane From INSTALL: ... Client-only installation: If you want to install only the client applications and interface libraries, then you can use these commands: gmake -C src/bin install gmake -C src/include install gmake -C src/interfaces install gmake -C doc install ... src/bin has pg_ctl et al. Maybe encourage is a strong word... ;-) Just moving pg_ctl to the server package and let the server package depend on libpq.so.5 should help in this case, I guess... /Palle ---(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] error messages without schema name
"=?UTF-8?Q?Filip_Rembia=C5=82kowski?=" <[EMAIL PROTECTED]> writes: > I heard from my PostgreSQL teacher, that all such messages should be > corrected in the source code, to include schema name too. I doubt we'd ever do that, because in most scenarios it'd just be unnecessary clutter, and the primary error messages are supposed to be kept short. What probably should happen someday is to add additional fields to error reports that carry object name, schema name, and so forth. I think these wouldn't be displayed normally but you could always turn on "verbose" mode if you needed to see them. This approach is needed to support applications that want to know, for example, which constraint failed during an INSERT without having to try to extract it from the possibly-translated message text. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] dynamic plpgsql question
Marc Evans wrote:
On Wed, 13 Dec 2006, Erik Jones wrote:
Marc Evans wrote:
Hi -
I am struggling with a trigger function in plpgsql, and am hoping
that someone on this list can't show me a way to do what I need.
In the trigger, TG_ARGV[0] is the name of a column that I want to
evaluate. This code shows the concept, though is not functional:
CREATE OR REPLACE FUNCTION foo() RETURNS TRIGGER AS $$
DECLARE
column_name TEXT := TG_ARGV[0];
data TEXT;
BEGIN
EXECUTE 'SELECT NEW.' || column_name INTO data;
-- ...
END;
$$ LANGUAGE plpgsql;
When I try to use that code, I receive:
c3i=> insert into test_table values (1,1);
ERROR: NEW used in query that is not in a rule
CONTEXT: SQL statement "SELECT NEW.magic"
How can I get the value of NEW.{column_name} (aka NEW.magic in this
specific test case) into the variable data?
EXECUTE 'SELECT ' || NEW.column_name ';' INTO data;
Thanks for the suggestion. Unfortunately, it does not work:
CREATE OR REPLACE FUNCTION foo() RETURNS TRIGGER AS $$
DECLARE
column_name TEXT := TG_ARGV[0];
data TEXT;
BEGIN
EXECUTE 'SELECT ' || NEW.column_name || ';' INTO date;
-- ...
END;
$$ LANGUAGE plpgsql;
c3i=> insert into test_table values (1,1);
ERROR: record "new" has no field "column_name"
Ah, sorry, I'd just arrived at work and wasn't quite away as of yet.
AFAIK, plpgsql doesn't have any facilities for variable substitution in
variable names (called variable variables in some languages). However,
if plpgsql is your only procedural option (plperl, I've heard, does
support this feature) and the possible values for column name are known
to you, there is a hackish workaround:
IF(column_name = 'foo') THEN
EXECUTE 'SELECT ' || NEW.foo || ';' INTO data;
ELSIF(column_name = 'bar') THEN
EXECUTE 'SELECT ' || NEW.bar || ';' INTO data;
ELSIF
.
.
.
You get the picture...
--
erik jones <[EMAIL PROTECTED]>
software development
emma(r)
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
Re: [GENERAL] grant select on all tables of schema or database
2006/12/13, Tom Lane <[EMAIL PROTECTED]>: Martijn van Oosterhout writes: > I don't beleive you have to explicitly grant access to the database, or > the schema, but you definitly have to grant access to the tables > directly. They're completely separate privileges. GRANT ON DATABASE grants or revokes permissions associated with database-level operations, not permissions on specific objects contained in the database. Likewise for GRANT ON SCHEMA. What the OP seems to be wishing for is a wild-card grant operation, viz GRANT ALL ON TABLE *.* TO joeblow which would indeed be a useful thing to have, but it's *not* GRANT ON DATABASE. Exactly. Thank you Martijn and Tom for the help and clarification. Cheers, t.n.a. ---(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] Online index builds
On Wed, 2006-12-13 at 17:12, Tom Lane wrote: > Csaba Nagy <[EMAIL PROTECTED]> writes: > > So an implementation which optimistically builds the new index > > concurrently while holding no lock, and then hopes for the 3rd > > transaction to be able to get the exclusive lock and be able to swap the > > new index in the place of the old index, and error out if it can't - it > > is perfectly acceptable. > > It would maybe be acceptable if there were a way to clean up the mess > after a failure, but there wouldn't be ... With the "mess" you refer to the new index, and the fact it is impossible to delete it if not possible to replace the old one ? I fail to see why... you WILL get an exclusive lock, so you should be able to delete the index. The deadlock is not an issue if you release first the shared locks you hold... If "mess" means that it's impossible to tell that you can or can't safely replace the index, then that's a problem, but I think the scenarios you thought out and would break things are detectable, right ? Then you: take the exclusive lock, check if you can still safely replace the index, do it if yes, delete the new index otherwise or on failure to swap (to cover unexpected cases). If you can't delete the new index cause somebody changed it in the meantime (that must be a really strange corner case), then bad luck, nobody is supposed to do that... While I'm not familiar enough with how postgres handles locking, wouldn't be also possible for DDLs to first also acquire a lock which would only lock other DDLs and not DMLs ? In that case you could get that lock first and hold it through the second phase, and make the second phase also swap the indexes after also acquiring the full exclusive lock. That could potentially still deadlock, but the chance to do so would be a lot smaller. I think the above is not clear enough... what I mean is to make all DDLs get 2 locks: - first an "DDL exclusive" lock which blocks other DDLs from getting the same; - second a full exclusive lock which blocks any other locks; Between the 2 there could go some operation which is not blocking normal operation but needs protection from other concurrent DDL. If only DDLs do this and always in this order, there's no deadlock potential. Disadvantage is the very need to place one more lock... Cheers, Csaba. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] [EMAIL PROTECTED]: Re: port fault on pg_ctl's place]
Palle Girgensohn <[EMAIL PROTECTED]> writes: > From INSTALL: > ... >Client-only installation: If you want to install only the client >applications and interface libraries, then you can use these >commands: > gmake -C src/bin install > gmake -C src/include install > gmake -C src/interfaces install > gmake -C doc install > ... > src/bin has pg_ctl et al. Maybe encourage is a strong word... ;-) Actually there's quite a lot of stuff under src/bin that has no value in a client-only installation: initdb, ipcclean, pg_controldata, pg_resetxlog ... probably those instructions need some amendment. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] Weird PG 8.1.3 date error
Hi, I have a user who is getting this error on a 8.1.3 server when importing some data from a dbf file: PostgreSQL Error Code: (1) ERROR: date/time field value out of range: "08-01-2006" He says the db is SQL-ASCII and the datestyle is at the default. Of course on all my test servers the insert works perfectly without the range error. Anyone have any Ideas? I don't know if the pg server is win32 or Unix. Thanks, -- Tony ---(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] grant select on all tables of schema or database
That would indeed be a handy feature... I was surprised when I discovered this was not available like in mysql, it's a real pain to grant permissions to a bunch of tables without querying table metadata. GRANT ALL ON TABLE *.* TO joeblow which would indeed be a useful thing to have, but it's *not* GRANT ON DATABASE. regards, tom lane ---(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 -- Gene Hart cell: 443-604-2679
Re: [GENERAL] grant select on all tables of schema or database
Martijn van Oosterhout writes: > I don't beleive you have to explicitly grant access to the database, or > the schema, but you definitly have to grant access to the tables > directly. They're completely separate privileges. GRANT ON DATABASE grants or revokes permissions associated with database-level operations, not permissions on specific objects contained in the database. Likewise for GRANT ON SCHEMA. What the OP seems to be wishing for is a wild-card grant operation, viz GRANT ALL ON TABLE *.* TO joeblow which would indeed be a useful thing to have, but it's *not* GRANT ON DATABASE. regards, tom lane ---(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] error messages without schema name
06-12-13, Shoaib Mir <[EMAIL PROTECTED]> napisał(a): You can not truncate table 'foo' because there is referential integrity between 'foo' and 'bar', so try using TRUNCATE nsp2.foo CASCADE; You can find the schema name for 'bar' using a query like: select nspname from pg_namespace where oid = (select relnamespace from pg_class where relname = 'bar'); this returns 2 schema names. which one is of my "bar"? Actually, I was asking if this behaviour of postgres is OK? I heard from my PostgreSQL teacher, that all such messages should be corrected in the source code, to include schema name too. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] dynamic plpgsql question
On Wed, 13 Dec 2006, Erik Jones wrote:
Marc Evans wrote:
Hi -
I am struggling with a trigger function in plpgsql, and am hoping that
someone on this list can't show me a way to do what I need.
In the trigger, TG_ARGV[0] is the name of a column that I want to evaluate.
This code shows the concept, though is not functional:
CREATE OR REPLACE FUNCTION foo() RETURNS TRIGGER AS $$
DECLARE
column_name TEXT := TG_ARGV[0];
data TEXT;
BEGIN
EXECUTE 'SELECT NEW.' || column_name INTO data;
-- ...
END;
$$ LANGUAGE plpgsql;
When I try to use that code, I receive:
c3i=> insert into test_table values (1,1);
ERROR: NEW used in query that is not in a rule
CONTEXT: SQL statement "SELECT NEW.magic"
How can I get the value of NEW.{column_name} (aka NEW.magic in this
specific test case) into the variable data?
EXECUTE 'SELECT ' || NEW.column_name ';' INTO data;
Thanks for the suggestion. Unfortunately, it does not work:
CREATE OR REPLACE FUNCTION foo() RETURNS TRIGGER AS $$
DECLARE
column_name TEXT := TG_ARGV[0];
data TEXT;
BEGIN
EXECUTE 'SELECT ' || NEW.column_name || ';' INTO date;
-- ...
END;
$$ LANGUAGE plpgsql;
c3i=> insert into test_table values (1,1);
ERROR: record "new" has no field "column_name"
- Marc
---(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] Online index builds
Csaba Nagy <[EMAIL PROTECTED]> writes: > So an implementation which optimistically builds the new index > concurrently while holding no lock, and then hopes for the 3rd > transaction to be able to get the exclusive lock and be able to swap the > new index in the place of the old index, and error out if it can't - it > is perfectly acceptable. It would maybe be acceptable if there were a way to clean up the mess after a failure, but there wouldn't be ... regards, tom lane ---(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] dynamic plpgsql question
Marc Evans wrote:
Hi -
I am struggling with a trigger function in plpgsql, and am hoping that
someone on this list can't show me a way to do what I need.
In the trigger, TG_ARGV[0] is the name of a column that I want to
evaluate. This code shows the concept, though is not functional:
CREATE OR REPLACE FUNCTION foo() RETURNS TRIGGER AS $$
DECLARE
column_name TEXT := TG_ARGV[0];
data TEXT;
BEGIN
EXECUTE 'SELECT NEW.' || column_name INTO data;
-- ...
END;
$$ LANGUAGE plpgsql;
When I try to use that code, I receive:
c3i=> insert into test_table values (1,1);
ERROR: NEW used in query that is not in a rule
CONTEXT: SQL statement "SELECT NEW.magic"
How can I get the value of NEW.{column_name} (aka NEW.magic in this
specific test case) into the variable data?
EXECUTE 'SELECT ' || NEW.column_name ';' INTO data;
--
erik jones <[EMAIL PROTECTED]>
software development
emma(r)
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
Re: [GENERAL] A VIEW mimicing a TABLE
Rafal Pietrak <[EMAIL PROTECTED]> writes: > I can see that with a VIEW, I can do prity mutch everything I can do > with a TABLE, so a VIEW mimics a TABLE quite well but one feature: a > default value for a row on INSERT. You can add a default to a view's column, either the same as the underlying table's default, or different if you want. ALTER TABLE view ALTER COLUMN col SET DEFAULT expr regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] [EMAIL PROTECTED]: Re: port fault on pg_ctl's place]
Gergely CZUCZY <[EMAIL PROTECTED]> writes: > This is wierd, I agree. The split between server and client is really made = > in the postgresql sources, > I just follow their guidelines. [ checks RPM specfiles ... ] Both Devrim and I put pg_ctl in the server subpackage, dunno where you think that the sources encourage something else ... regards, tom lane ---(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
[GENERAL] dynamic plpgsql question
Hi -
I am struggling with a trigger function in plpgsql, and am hoping that
someone on this list can't show me a way to do what I need.
In the trigger, TG_ARGV[0] is the name of a column that I want to
evaluate. This code shows the concept, though is not functional:
CREATE OR REPLACE FUNCTION foo() RETURNS TRIGGER AS $$
DECLARE
column_name TEXT := TG_ARGV[0];
data TEXT;
BEGIN
EXECUTE 'SELECT NEW.' || column_name INTO data;
-- ...
END;
$$ LANGUAGE plpgsql;
When I try to use that code, I receive:
c3i=> insert into test_table values (1,1);
ERROR: NEW used in query that is not in a rule
CONTEXT: SQL statement "SELECT NEW.magic"
How can I get the value of NEW.{column_name} (aka NEW.magic in this
specific test case) into the variable data?
For completeness, I am using version 8.2.0.
Thanks in advance - Marc
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] PG Admin
On 12/4/06, Bob Pawley <[EMAIL PROTECTED]> wrote: Your missing the point. I am creating a design system for industrial control. The control devices need to be numbered. The numbers need to be sequential. If the user deletes a device the numbers need to regenerate to again become sequential and gapless. As I understand it, it really doesn't matter if the gap-less sequence is stored in the DB!! All you want is when you SELECT, the result should have gap-less sequennce of IDs associted to the device name, just as pgAdmid GUI is doing. If that is the case, then I think I have a solution. After a lot of thinking, and failed experiments with generate_series(), CREATE AGGREGATE, etc etc, a simple solution dawned upon me (yes, closing your laptop and think-walking in the open helps). Can the following query help you? postgres=# select (select count(*) from device i where i.name < o.name) +1 as ID, name from device o; id | name +- 1 | device0 2 | device1 3 | device2 4 | device3 5 | device4 6 | device5 7 | device6 8 | device7 9 | device8 10 | device9 (10 rows) postgres=# In case you do not have unique device names, you can create a serial column, and use that column in the count(*) subquery instead of the name. This looks like a ROWNUM pseudo-column in ORACLE's query results. Following is a complete test case: postgres=# create table device( id serial, name varchar(10)); NOTICE: CREATE TABLE will create implicit sequence "device_id_seq" for serial column "device.id" CREATE TABLE postgres=# insert into device(name) select 'device' || a from generate_series(0,9) as s(a); INSERT 0 10 postgres=# delete from device where mod(id,2) = 0; DELETE 5 postgres=# insert into device(name) select 'device' || a from generate_series(0,9) as s(a); INSERT 0 10 postgres=# delete from device where id >= 10 and mod(id,2) <> 0; DELETE 5 postgres=# insert into device(name) select 'device' || a from generate_series(0, 9) as s(a); INSERT 0 10 postgres=# select * from device; id | name +- 1 | device0 3 | device2 5 | device4 7 | device6 9 | device8 12 | device1 14 | device3 16 | device5 18 | device7 20 | device9 21 | device0 22 | device1 23 | device2 24 | device3 25 | device4 26 | device5 27 | device6 28 | device7 29 | device8 30 | device9 (20 rows) postgres=# select (select count(*) from device i where i.id < o.id) + 1 as rownum, id, name from device o; rownum | id | name ++- 1 | 1 | device0 2 | 3 | device2 3 | 5 | device4 4 | 7 | device6 5 | 9 | device8 6 | 12 | device1 7 | 14 | device3 8 | 16 | device5 9 | 18 | device7 10 | 20 | device9 11 | 21 | device0 12 | 22 | device1 13 | 23 | device2 14 | 24 | device3 15 | 25 | device4 16 | 26 | device5 17 | 27 | device6 18 | 28 | device7 19 | 29 | device8 20 | 30 | device9 (20 rows) postgres=# Hope this helps. Best regards, -- [EMAIL PROTECTED] [EMAIL PROTECTED] gmail | hotmail | yahoo }.com
[GENERAL] error messages without schema name
Hi all, I heard once that all messages which refer to table names should also give their schema name. I mean, if I see something like rzeznia=# TRUNCATE nsp2.foo; ERROR: cannot truncate a table referenced in a foreign key constraint DETAIL: Table "bar" references "foo". HINT: Truncate table "bar" at the same time, or use TRUNCATE ... CASCADE. I do not know which namespace "bar" belongs to. Is this a bug, or do I have to live with it? -- Filip Rembiałkowski ---(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
