Re: [GENERAL] help with "plpgsql"
On Apr 21, 2008, at 8:51 AM, Pau Marc Munoz Torres wrote: psql:/usr/local/Make2D-DB_II /pgsql/make2db_functions.pgsql:85: ERROR: language "plpgsql" does not exist HINT: Use CREATE LANGUAGE to load the language into the database. and then when I try to create the language, i get geldb=# CREATE LANGUAGE plpgsql; ERROR: language "plpgsql" already exists Are you certain that you are dealing with the same database in both situations? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Schema migration tools?
Greetings, We have the traditional three servers: dev --> staging --> production each with a PostgreSQL instance and the same schema, at least over time. Right now, we handle schema migration (updating the schema for new releases) by manually-created scripts that apply the changes to staging and production. I'd like a tool that would automatically create these scripts, and I wondered if anything like this existed. The theory would be that it would consider two databases a and b, and produce the appropriate script to change b's schema to match a. Does anything like this exist? If not, I might have a new project... Thanks! -- Christophe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to modify ENUM datatypes?
On Apr 22, 2008, at 1:45 PM, D. Dante Lorenso wrote: So, the advice here is "don't use ENUM"? I think it's more "Don't use ENUM for a type that you are planning to extend." -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Backup setup
For a database that big, you might consider using the WAL archiving strategy and shipping the WAL files offsite: http://www.postgresql.org/docs/8.3/interactive/continuous- archiving.html On Apr 23, 2008, at 8:14 AM, Gabor Siklos wrote: I need to back up our database off-site for disaster recovery. If I just back up the entire database data directory (i.e. /var/lib/ pgsql/data) will I be able to restore from there? Or should I instead just dump the data, using pg_dump, and back up the dump? The advantage of the first method would be that I would not have to wait for pg_dump (it takes quite long on our 60G+ database) and would just be able to configure the backup agent to monitor the data directory and do differential backups of the files there every hour or so. Your suggestions are much appreciated! -Gabor
[GENERAL] Unloading a table consistently
Hi, I will have a log table which, once a day or so, is copied to a file (for movement to a data warehouse), and the log table emptied. For performance, the log table on the production system has no indexes, and is write-only. (The unload process is the only reader.) To unload it, I will be doing: BEGIN; SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; COPY log TO 'filename-path'; TRUNCATE log; COMMIT; My understanding is that I need the SERIALIZABLE isolation level so that the COPY and TRUNCATE see exactly the same view of the table. Obviously, I don't want to lose data by having the TRUNCATE delete records that appeared while the COPY was executing. Is that correct? Is there a better way to handle this kind of thing that I'm missing? Thanks! -- Xof -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Unloading a table consistently
On May 3, 2008, at 9:56 AM, Tom Lane wrote: This is a great deal less efficient than TRUNCATE, but it's secure for concurrent insertions, which TRUNCATE is definitely not. Exactly my question; thank you! -- Xof -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Unloading a table consistently
On May 3, 2008, at 9:29 PM, Patrick TJ McPhee wrote: How about something along the lines of BEGIN; ALTER TABLE log RENAME to log_old; CREATE TABLE log(...); COMMIT; BEGIN; LOCK table log_old; COPY log_old TO 'filename-path'; DROP TABLE log_old; COMMIT; I believe this will keep the writers writing while keeping the efficiency of truncating. Brilliant. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] rounding problems
Yet another option, of course, is to simply not do any calculations in PostgreSQL, and accept the results from Excel as definitive... which seems to be what is desired, anyway. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Mediawiki 1.10 and PG 8.3 upgrade
You'll need to get a particular revision of MediaWiki that is PG 8.3 compatible: http://people.planetpostgresql.org/greg/index.php?/archives/123- MediaWiki-is-Postgres-8.3-compatible.html Once that's done, it works fine (at least for me). -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] "Rails and PostgreSQL" now up on media.postgresql.org
Greetings, The video from the June 9, 2009 SFPUG meeting, "PostgreSQL as a secret weapon for high-performance Ruby on Rails applications," is now available for viewing or download from the media.postgresql.org server: http://media.postgresql.org/sfpug/sfpug-rails-20090609.mov Thanks, -- Xof -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] "Rails and PostgreSQL" now up on media.postgresql.org
On Jun 10, 2009, at 6:27 PM, Chris wrote: Can you list the filesizes on http://media.postgresql.org/sfpug/ please? Josh Berkus handles that page, but I think that's a splendid idea. This particular video file is about 403MB. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] search for partial dates
On Jun 11, 2009, at 1:23 PM, James B. Byrne wrote: Given a datetime column, not null, is there a single syntax that permits searching for all dates in a given year, year+month, and year+month+day such that a single parameterised query can handle all three circumstances? Well, of course, in a trivial sense: SELECT * FROM the_table WHERE datetimecolumn >= $1 AND datetimecolumn <= $2; The application has to create the appropriate values for the first and last days of the year or month in this case, but it's a rare language that doesn't that facility. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] commercial adaptation of postgres
On Jul 20, 2009, at 6:56 PM, Dennis Gearon wrote: I once talked to a company that made a custome version of Postgres. It split tables up on columns and also by rows, had some other custome features. It was enormously faster from what I gathered. I could of sworn it began with the letter 'T', but maybe not. I don't see anything like that on the commercial page of the posgres site. Truviso? http://truviso.com/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] 3des key lengths and key management
On Jul 23, 2009, at 12:11 PM, Steve Atkins wrote: They asked me to open up my firewall to them, pointing at a fake server, just so they'd have something to audit, after failing our audit "because we only allowed access to the application from inside our firewall." I'm glad it wasn't just me. We failed our audit because our firewall correctly detected their probes as an intrusion, and shut them down. It would be as if your home failed a security audit because no one could get through the external fence, so "we couldn't verify that the door was locked." -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] To store and retrive image data in postgresql
On Jul 7, 2008, at 12:22 PM, aravind chandu wrote: I need to store an image in postgresql database and after that i need to retrive the image back.Can you please help me how to do this? Assuming you mean an image as in a binary visual image (like a JPEG), the data type you want is BYTEA. How to actually get BYTEA data into and out of PostgreSQL depends on what client library and language you are using. If your application allows it, it is often far more efficient to store a reference to the image in the database (such as a URI or file path), rather than the image itself. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] rollback
On Jul 9, 2008, at 6:38 AM, Adrian Moisey wrote: I would like to be able to "mark" a point in my postgres database. After that I want to change a few things and "rollback" to that point. Does postgres support such a thing? Is it possible for me to do this? This seems to be exactly what transactions are designed to do. Could you tell us a bit more about the application and why a transaction isn't the right solution? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Default UUID in Postgres
On Jul 9, 2008, at 4:32 PM, Swaminathan Saikumar wrote: Hello, I created a Postgres table with a UUID. I want the UUID to be populated by default. PostgreSQL doesn't have built-in functions for generating UUIDs, but there is a module in contrib that will do so: http://www.postgresql.org/docs/current/static/datatype-uuid.html -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] php + postgresql
On Jul 25, 2008, at 11:20 AM, Andrew Sullivan wrote: On Fri, Jul 25, 2008 at 01:41:50PM -0400, Jonathan Bond-Caron wrote: I'd say "the Web is just and always was a hack" I have to object to this pretty strongly. He has a point, though. If you were starting out to build a user interface framework for building applications to be used by general users, I really doubt you'd end up with the current situation of HTTP, HTML, CSS, Javascript. But that's no matter, really, because here we are. Same for PHP. If you wanted to build a great, elegant, scripting language for writing web front ends, you almost certainly would not end up with PHP. But, here we are. Coming from a C++ and Java background, I find PHP to be just nasty in a lot of ways, but it gets the job done. Most developers don't make deep informed decisions about PHP vs other languages. They use it because everyone else is, there is a huge ecosystem of support around it, it's easy to get something flopping around on the table quickly, and they know *for sure* that they can host it anywhere. Which, really, are not terrible reasons to pick a development environment. Dragging the subject back to PostgreSQL, it's the same thing with MySQL vs PG. Very few people do detailed technical analyses of exactly which DB to use (and, if they do, they use PG :) ). They use MySQL because everyone else does, it gets the job done (or at least appears to), and, most importantly, every $9.95/month hosting plan in the world includes MySQL because Wordpress requires it. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Fetch for Update
On Jul 28, 2008, at 9:16 AM, Bob Pawley wrote: I haven't been able to find much information on Fetch for Update. Does 8.3 support this command?? Postgres doesn't have an explicit FETCH FOR UDPATE. You can either create the cursor with SELECT FOR UPDATE, or UPDATE the row in the cursor using UPDATE ... CURRENT OF. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How do I set up automatic backups?
On Jul 29, 2008, at 1:24 PM, Rob Richardson wrote: I was asked how to automate the procedure, and I couldn't answer. The options are manifold! http://www.postgresql.org/docs/8.3/interactive/backup.html -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Cursor
On Jul 29, 2008, at 2:35 PM, Tom Lane wrote: No, he does need an OPEN. Really? I thought that PG didn't use OPEN: "The PostgreSQL server does not implement an OPEN statement for cursors; a cursor is considered to be open when it is declared." http://www.postgresql.org/docs/8.3/interactive/sql-declare.html -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Cursor
On Jul 29, 2008, at 4:51 PM, Klint Gore wrote: It's different in PL/pgSQL. Ah, yes, sorry, didn't catch that it was a PL/pgSQL function. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] PL/pgSQL equivalent to PQtransactionStatus?
My apologies if this is in the docs and I missed it, but is there a PL/pgSQL function equivalent for the pglib function PQtransactionStatus (i.e., a way to find out if we're in an open transaction block, and if that transaction is in an error status)? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PL/pgSQL equivalent to PQtransactionStatus?
On Jul 31, 2008, at 11:12 AM, Richard Huxton wrote: A pl/pgsql function *always* executes within a transaction. Indeed so. What I'm looking for is a way of detecting if a transaction block has been opened (i.e., we're within a BEGIN). -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PL/pgSQL equivalent to PQtransactionStatus?
On Jul 31, 2008, at 11:49 AM, Alvaro Herrera wrote: Why does it matter? I'm attempting to "clean out" a connection that is in an unknown state (along the lines of what pgpool does when reusing an open connection). Of course, I could just fire an ABORT down, but it seems nicer to avoid doing so if no transaction block is open. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PL/pgSQL equivalent to PQtransactionStatus?
On Jul 31, 2008, at 11:49 AM, Alvaro Herrera wrote: Why does it matter? Ah, I see, deep confusing on my part regarding PL/pgSQL and tranasctions! Ignore question. :) -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] uuid
On Aug 3, 2008, at 1:18 PM, [EMAIL PROTECTED] wrote: ERROR: function uuid_ns_url() does not exist Remember to install the functions in your database using the SQL file in the contrib/uuid-ossp directory, uuid-ossp.sql. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Running a PL/pgSQL function
I'm startled that I've never done this before, but... I have a PL/ pgSQL function that takes no arguments, returns VOID, and has a bunch of side effects on the database. The correct way of invoking this function is: SELECT my_func(); ... yes? Thanks; it seems to work fine, but using SELECT here is causing some part of my brain to scream "counter-intuitive." -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] plpgsql functions
On Aug 15, 2008, at 1:47 PM, Raymond O'Donnell wrote: For functions return SETOF any type, you need to use the following idiom: Or, you can use, RETURN QUERY -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Postgre connect on Postgre
On Aug 26, 2008, at 7:10 PM, Anderson dos Santos Donda wrote: How I can connect a postgre database on another postgre database, and manipulate the datas on both database? There is a module in contrib just for such a purpose: http://www.postgresql.org/docs/8.3/interactive/dblink.html -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] MySQL LAST_INSERT_ID() to Postgres
On Aug 28, 2008, at 3:23 PM, D. Dante Lorenso wrote: I use RETURNING for all my insert and UPDATE statements now. Usually I'll return the primary key for the table, but sometimes I return a column that is created by one of my triggers. It's awesome to be able to do this in one query. Word. My current pet architecture is to set up Postgres like an application server (the web front end just call PL/pgSQL stuff rather than doing direct SQL), and this makes my life much easier for that. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] indexes on functions and create or replace function
On Aug 28, 2008, at 3:21 PM, Matthew Dennis wrote: I have no doubt that someone would complain about it, but I think it's better than the alternative. Determining if changing any function will cause an index to break is not a straight-forward problem. I don't believe that PG right now keeps a graph of which functions call which, so replacing a function deep in the logical calling hierarchy could break the index as easily as one mentioned at the top. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] indexes on functions and create or replace function
On Aug 28, 2008, at 5:49 PM, Matthew Dennis wrote: Yes, I can see that would indeed be a problem. Are there future plans to start tracking such dependencies? It seems like it would be a good idea in general. I believe the EXECUTE statement would thwart such plans. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] indexes on functions and create or replace function
On Aug 28, 2008, at 6:10 PM, Matthew Dennis wrote: I'm not sure I follow. Couldn't you track which statements were prepared that called a function and either reprepare (just like reindex, recheck, etc) or in the case of dropping a function, refuse to drop it because something depends on it? EXECUTE in PL/pgSQL accepts a string and executes it as a statement. (That's different from the SQL-level EXECUTE.) I'm not sure how one would track dependencies there. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] indexes on functions and create or replace function
On Aug 28, 2008, at 6:51 PM, Alvaro Herrera wrote: Also, you have to keep in mind that we support pluggable languages. The function's source code is just an opaque string. Oh, ouch, right. I think that this is one of those cases where it's better that we simply advertise: BE AWARE OF THIS, rather than try to provide a half- baked solution that gives the illusion of safety without the reality. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] indexes on functions and create or replace function
On Aug 28, 2008, at 7:04 PM, Matthew Dennis wrote: The plpgsql execute statement, as I understand it, means "take this string and execute like a client sent it to you". Of course, the string could come from anywhere. There's no inherent reason that I can think of (except good taste) that you could not write a function that retrieved a string out of a field in a table, executed it, and returned that as a value for use in an index. The client didn't send the string along, but it still creates dependencies. Anyway, as was correctly pointed out, tracking dependencies in PL/ pgSQL is bad enough, but PL/Java, PL/Perl... -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] indexes on functions and create or replace function
On Aug 28, 2008, at 7:22 PM, Matthew Dennis wrote: Yes, but in the case of pluggable languages, you still load something that constitutes the "source". In the case of PL/Java, the jar for example. This would mean that, for example, if you changed any single function (no matter how distant and irrelevant to the one used to create a functional index), the jar would change, so we would have to invalidate all functional indexes written using functions contained in that jar. I'd certainly rebel at that: It would make deploying a new version of the jar very expensive, and unavoidably so. I'd have to say that the current situation falls into the category of "slightly annoying," but it has the benefit that whether or not to rebuild the index is left up to me. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] ERROR: relation . . . does not exist
On Aug 30, 2008, at 6:26 AM, Albretch Mueller wrote: Well, then obviously there is the need for it and you were not successful enough at convincing these developers that they were "confusing postgresql with a spreadsheet program" The behavior you are looking for is typical of a spreadsheet, because spreadsheet programs (generally; I'm sure there are exceptions) don't have the notion of a schema; each cell can hold its own particular type. That being said, the automatic type-guessing that Excel, say, provides is far from foolproof; I've probably spent more time cleaning up Excel's bad guesses than would have been saved by my just specifying a type for each column. As has been noted, text representation of values are extremely ambiguous as of which Postgres type they mean... and, of course, you could have user-defined domains and types as well. It's true that it could take a wild guess, but that's not a traditional part of a database engine. That being said, it would not be too hard to write a client that accepted a CSV or tab-delimited file, parsed the header into column names, and then scanned the values of the columns to take a reasonable guess as to the column type from a highly limited set of possibilities. This is probably one of those classic "twenty lines of Perl" problems. It doesn't seem as though COPY INTO is the right place for that, since the particular guesses and set of types that one would make strike me as very closely tied to your particular application domain. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] ERROR: relation . . . does not exist
On Aug 30, 2008, at 10:33 AM, Albretch Mueller wrote: well, yeah! I would totally agree with you, but since I doubt very much "COPY FROM CSV" is part of the SQL standard to beging with, why not spice it up a little more? I'd guess that coming up with a general algorithm to guess the type from a column of CSV text would satisfy no one, since we'd always miss a particular case that is important to someone (are times local or UTC? Should we assume integer or float? Varchar or text?), and the option is a forest of switches that would be extremely complex and error prone. This sounds very much like an application-domain problem, best solved in the application domain. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] ERROR: relation . . . does not exist
You have made clear to me why my attempt for a RFE for COPY FROM CVS has found some technical resistance/disagreement, but I still think my idea even if not so popular for concrete and cultural reasons makes at least sense to some people It's a perfectly reasonable problem to want to solve; the question is whether COPY is the right place to solve it. I would think that a tool that reads the CSV data and produces a proposed schema definition for the table would be a more generally-useful approach. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Oracle and Postgresql
On Aug 31, 2008, at 7:44 PM, Guy Rouillier wrote: CTOs/CIOs like to sleep at night. If you buy Oracle, and there's a problem, the conversation with the CEO is that "Oracle broke." With PG, even if you have exactly the same level of support, "that database you selected broke." The sad reality is that choosing something with a high industry presence makes it easier to offload blame for failures, especially when dealing with non-technical management. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Postgres does not start, gives no error
On Sep 4, 2008, at 11:34 AM, Akhtar Yasmin-B05532 wrote: I am really stuck here. And need to get a way thru all of this. Any suggestions will be really appreciated. Have you confirmed that the user that you are logged in as when you attempt to start Postgres has write access to /home/data/www/pg7/data? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: Obfuscated stored procedures (was Re: [GENERAL] Oracle and Postgresql)
On Sep 25, 2008, at 1:05 PM, Casey Allen Shobe wrote: As for the expectation above - could pl/pgsql be made compilable? Without getting into the argument as to the level of security provided, it strikes me that a reasonable approach would be a non- core pluggable language which accepts encrypted strings as functions, decrypts them (using a key compiled into the language module), and passes them on to PL/pgSQL for execution. This would keep the functionality out of core, allow the developer who distributes the code to plop in their own key and distribute the code as a compiled module, and minimizes reimplementation. This would, of course, be easily hacked with someone who can step through the language module with a debugger, but I don't see any reasonable way of preventing someone with that level of access from breaking the code without OS-level support. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] postgresql and Mac OS X
On Oct 28, 2008, at 4:03 PM, Steve Atkins wrote: Installing from source means I can avoid the fragility of macports or fink, and know that I've built it in much the same way as the postgresql or solaris installation I'd be using for production. +1 It means I can easily pick the contrib modules I'm interested in. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Are there plans to add data compression feature to postgresql?
On Oct 30, 2008, at 8:10 AM, Grzegorz Jaśkiewicz wrote: up to 8.3 it was massively slower on raid1 (software raid on linux), starting from 8.3 things got lot lot better (we speak 3x speed improvement here), but it still isn't same as on 'plain' drive. I'm a bit surprised to hear that; what would pg be doing, unique to it, that would cause it to be slower on a RAID-1 cluster than on a plain drive? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Decreasing WAL size effects
On Oct 30, 2008, at 2:54 PM, Gregory Stark wrote: Wouldn't it be just as good to indicate to the archive command the amount of real data in the wal file and have it only bother copying up to that point? Hm! Interesting question: Can the WAL files be truncated, rather than zeroed, safely? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Question about the WITH RECURSIVE patch
On Nov 20, 2008, at 1:21 PM, Tom Lane wrote: See the fine manual, for instance last para here: http://developer.postgresql.org/pgdocs/postgres/queries-with.html Fine manual indeed... this the best explanation of WITH RECURSIVE I've ever read. Kudos to the documentation writer(s). -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] auditing pg_hba.conf
Hi Why don't use etckeeper ? Regards, http://joey.kitenet.net/code/etckeeper/ Le 03/11/09 23:41, JP Fletcher a écrit : Hi, We manage hundreds of clusters and a handful of distinct pg_hba.conf files across several sites. We are mostly satisfied with our automated method of management, but on occasion, someone will hand edit a pg_hba.conf file, and some application will get locked out. This a bad. We'd like to be able to do a few things related to auditing pg_hba.conf: 1. Store a copy of pg_hba.conf on server start or reload 2. Have an audit trail that shows when particular rules were loaded. 3. Compare the contents of pg_hba.conf to the rules that are actually loaded. 4. Alert the DBA when the rules loaded differ from the file that was previously loaded. We can accomplish #1 and #2 by having a shell command copy the file, or by storing rules in a db table. I'm not sure that #3 and #4 are possible until we accomplish #1. I'm not aware of any function or catalog table/view that stores pg_hba rules. I'm curious to know if anyone has any suggestions, or has solved a similar problem. Best Regards, JP -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How are locks managed in PG?
Playing the straight man, I have to ask: Scalability issues with locks in PG vs Oracle? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Running untrusted sql safely?
On Feb 15, 2009, at 2:47 PM, Stuart McGraw wrote: I just hoping for some confirmation that the permissions based approach did not have some holes in it that I am not seeing. Another possibility is to create a set of functions that contain the query operations you would like to allow, isolate those in a schema, and make that schema the only thing accessible to the (semi-)trusted users. Generally, I try to design things so that web servers and other clients who could potentially be compromised don't execute full- function SQL directly, but go through functions instead. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] pgsql announce now on twitter
On Mar 4, 2009, at 9:54 AM, Joshua D. Drake wrote: Not sure what the complainer is talking about here. pgsql-announce is moderated so spam should be almost nil. I'm not 100% sure what "Twitter spam" *is*, for that matter. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Streaming PUG meeting: PostgreSQL Genetics!
On Mar 11, 2009, at 5:51 PM, CaT wrote: Will there be a saved version of this available for later viewing? Don't make me choose between steak and beer and postgres. 8( Yes! I'll announce it here when it's available. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Maximum transaction rate
On Mar 13, 2009, at 11:59 AM, Joshua D. Drake wrote: Wait, actually a good BBU RAID controller will disable the cache on the drives. So everything that is cached is already on the controller vs. the drives itself. Or am I missing something? Maybe I'm missing something, but a BBU controller moves the "safe point" from the platters to the controller, but it doesn't move it all the way into the OS. So, if the software calls fsync, but fsync doesn't actually push the data to the controller, you are still at risk... right? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Video from the 2009-03-11 SFPUG talk on Unison, by Reese Hart
Hi, The video is now available for download! You can find it at: http://blog.thebuild.com/sfpug/sfpug-unison-20090311.mov Thanks, -- Christophe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Re: [pgsql-advocacy] Video from the 2009-03-11 SFPUG talk on Unison, by Reese Hart
On Mar 17, 2009, at 9:57 PM, Joshua D. Drake wrote: It is also on Vimeo: http://www.vimeo.com/3732938 Joshua D. Drake Thanks! - Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Accent insensitive search?
What I've done in the past in this situation is to create a separate field with the text normalized to whatever the search form is (all lower case, accents stripped, etc.), and then index and search that from the application. Although I've not tried it, a functional index that did the same thing might work, as long as you were careful in how the queries were written to ensure that it was used. - Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Accent insensitive search?
On Mar 18, 2009, at 11:24 AM, Alvaro Herrera wrote:
Hmm, if to_ascii() doesn't work, that's something worth some research.
Maybe the encoding config is broken, for example.
The docs say to_ascii() only works with LATIN1, LATIN2, LATIN9, and
WIN1250; maybe convert('string', 'UTF-8', 'SQL_ASCII')?
-
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] unexpected check constraint violation
On Mar 23, 2009, at 1:41 PM, Jeremy Harris wrote: Because equality is not well-defined for "real" values? That was my first thought, too, but why would two identical real literals evaluate to different bit patterns? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Connect without specifying a database?
On Apr 11, 2009, at 10:15 AM, li...@mgreg.com wrote: So, how does needing to connect to a database before querying about existing databases make any sense? Well, you have to connect to the database server, no matter what, in order to check on the existence of a database (unless you are doing it by groveling around in the data directories). The fact that you have to supply a database as part of that connection seems unremarkable. Suppose the documentation said something along these lines: "All connections are to a specific database. For operations in which a user-created database would be inappropriate (such as checking for the existence of a user-created database), the database 'root' is created by initdb as a known default for such operations." That would seem to be utterly uncontroversial and normal, yes? OK s/root/template1/, and you're set. :) -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] "PostgreSQL in the cloud" now up on media.postgresql.org
Greetings, The video from the April 8, 2009 SFPUG meeting, "PostgreSQL in the Cloud," is now available for viewing or download from the media.postgresql.org server: http://media.postgresql.org/sfpug/sfpug-cloud-20090408.mov Thanks to Josh Berkus for his organizational talents, and Dirk Jagdmann for running the live streaming during the event. -- Xof -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] "PostgreSQL in the cloud" now up on media.postgresql.org
On Apr 13, 2009, at 1:59 PM, Ivan Sergio Borgonovo wrote: wget says it is a 1Gb file. Is there anything smaller? Since I have the master right here, I'll be happy to reencode it to a smaller size (250mb is probably the reasonable lower limit before the video quality reaches the point that we might as well just post the audio :) ). -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] "PostgreSQL in the cloud" now up on media.postgresql.org
On Apr 13, 2009, at 2:54 PM, Joshua D. Drake wrote: Yes Vimeo can download and I will have it up soon. Thank you! My work here is done. :) -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Yet another "drop table vs delete" question
On Apr 21, 2009, at 12:28 PM, Peter Eisentraut wrote: Yes, but if you are asking that question, you probably really want to use TRUNCATE. The advantage of DROP TABLE being, of course, that DROP TABLE is transactionally-safe, while TRUNCATE is not. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Yet another "drop table vs delete" question
On Apr 21, 2009, at 1:20 PM, Tom Lane wrote: They're both going to drop data that might conceivably be visible in the snapshot of some concurrent transaction that hasn't yet touched the table (else it would have lock) but might wish to do so later. Unless I'm deeply misunderstanding something (always a possibility), DROP TABLE and TRUNCATE are not symmetrical in this regard. Once a transaction has issued a DROP TABLE, all other transactions that attempt to modify it are going to block on the first transaction's ACCESS EXCLUSIVE lock until it commits or aborts. In the case of TRUNCATE, the other transactions will see the table as being empty from the moment in the first transaction issues the TRUNCATE, and will see the rows reappear if the first transaction rolls back. Yes? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Yet another "drop table vs delete" question
On Apr 21, 2009, at 1:36 PM, Tom Lane wrote: I was thinking of MVCC semantics, which is a different issue. Indeed so, my error. This is a bit of a drift off-topic, but rereading the docs, I'm now having trouble visualizing the real-world effect of the non-MVCC-safeness of TRUNCATE. A transaction that queries the table before the TRUNCATE is going to pick up an ACCESS SHARED lock, which will prevent the TRUNCATE from running until it's released. The TRUNCATE will pick up an ACCESS EXCLUSIVE lock that will block any subsequent queries until the transaction doing the TRUNCATE commits. I'm sure there is a scenario under which a separate transaction could see non-MVCC behavior from TRUNCATE, but I'm having trouble see what it is. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Yet another "drop table vs delete" question
On Apr 21, 2009, at 2:15 PM, Jeff Davis wrote: In Session1, the serializable transaction sees an empty version of bar, even though it had tuples in at the time Session1 got its serializable snapshot. Indeed so, and I understand that part. But since Session1 didn't try to access 'bar', it can't distinguish that sequence from: Session2: BEGIN; TRUNCATE bar; COMMIT; Session1: BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE; SELECT * FROM foo; SELECT * from bar; COMMIT; I've been trying to come up with a scenario in which a TRUNCATE violates concurrency expectations; I'm sure one exists, but my brain isn't wrapping around it. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PostgreSQL Object-Oriented Database?
On Apr 27, 2009, at 7:00 AM, Robert Pepersack wrote: My agency has a contractor that created a PostgreSQL database that he calls "object-oriented". I noticed that the contractor has more than one value in a column separated by commas. In the relational world, this obviously violates first normal form. When I asked about it, he said that it's because PostgreSQL is an "object- oriented database". I'm very skeptical. Without knowing the details of the design, it's hard to comment on the design's strengths or weaknesses. However, there is nothing about PostgreSQL that requires any different design than one would use on any relational database. Arrays are, sometimes, a good solution for particular problems, and PostgreSQL has very fluent array functionality, but nothing about PostgreSQL requires their use versus more traditional relational designs. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] XML -> PG ?
On May 6, 2009, at 10:47 AM, Gauthier, Dave wrote: Is there a way to read an XML file into a postgres DB? I’m thinking that it will create and relate whatever tables are necessary to reflect whatever’s implied by the XML file structure. There's no built-in functionality that does what you describe, although building such a thing would be very straight-forward. There are many application-specific decisions you'd need to make (what level of hierarchy in the XML file corresponds to a database/schema/table, for example, and how to handle nested "fields" and missing ones).
Re: [GENERAL] Controlling psql output
On May 8, 2009, at 9:51 AM, Gauthier, Dave wrote: This gets rid of the header and footer OK. But there is still a blank line as the first line in stdout. Also, each record has a preceding space before the column value. Is there a way to do what I want? sed?
Re: [GENERAL] Column oriented pgsql
On May 8, 2009, at 11:25 AM, John R Pierce wrote: you read your tables by column, rather than by row?? SQL queries are inherently row oriented, the fundamental unit of storage is a 'tuple', which is a representation of a row of a table. I believe what is referring to is the disk storage organization, clustering a single column from multiple rows together onto a page. For example, if your typical use of a table is to read one particular column from a large number of rows, this could (in theory) improve performance. AFAIK, PostgreSQL doesn't support this. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] "PostgreSQL in the cloud" now up on media.postgresql.org
Greetings, The video from the May 12, 2009 SFPUG meeting, "BIRT & PostgreSQL," is now available for viewing or download from the media.postgresql.org server: http://media.postgresql.org/sfpug/sfpug-birt-20090512.mov Thanks, -- Xof -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] 8.3: timestamp subtraction
On May 23, 2009, at 9:13 AM, Daniel Verite wrote: I don't know why this query returns false: SELECT '20040506 070809.01'::timestamp(6) - '20010203 040506.007000'::timestamp(6) = '1188 day 3 hour 3 minute 3 second 3 millisecond'::interval; If I just subtract the two timestamps, its result is the interval I specified. What may cause this? It works for me: test=> SELECT '20040506 070809.01'::timestamp(6) - '20010203 040506.007000'::timestamp(6)= '1188 day 3 hour 3 minute 3 second 3 millisecond'::interval; ?column? -- t (1 row) Could this be due to the OP's build of PG using floating point timestamps? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] 8.3: timestamp subtraction
On May 23, 2009, at 10:44 AM, Havasvölgyi Ottó wrote: Thanks. I tested the standard Win32 distribution of 8.3.6. The same happens on 8.2. But on 8.0 it works. When I don't use milliseconds, then it works. Will 8.4 work fine on Win32 again? If the issue is using floating point timestamps, then the particular version of PostgreSQL isn't the issue; it's whether the distribution you were using was built with integer or floating point timestamps. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] do postgresql this job for me ? (firebird user)
On May 24, 2009, at 8:58 AM, Craig Ringer wrote: There isn't currently any REINDEX CONCURRENTLY option, but people seem to have a few workarounds that do the job if you really do need to rebuild an index on a live, active table. It's pretty straight-forward to do: CREATE INDEX CONCURRENTLY idx_new ON table ... ; DROP INDEX idx; ALTER INDEX idx_new RENAME TO idx; -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Bloated Table
On May 27, 2009, at 3:25 PM, Greg Smith wrote: Interesting and an extremely common request. I just added an item to the Vacuum section of the TODO list while you were listing issues and potential solutions here: "Provide more information in order to improve user-side estimates of dead space bloat in relations" links to this thread. Given this, is there is a recommended way of detecting bloat in indices automatically, rather than just looking at them once in a while and saying, "Hm, that looks pretty big"? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Index size
> On Dec 11, 2016, at 08:37, Melvin Davidson wrote: > > Yes, I see your point, but the case where the row does not fit into the same > block > would only occur with unlimited field types such as var[], bytea[], etc. No, that's not correct either. A block can fill up for any number of reasons: Dead tuples, a long HOT chain, or just a lot of data packed into the block. The first UPDATE to a tuple in a block that was populated by continuous INSERTs or a COPY, if the table is using the default fillfactor, will cause the new tuple to be written to a different block, which will cause an index update. The important thing to remember is that the process you are describing (in which indexes are not updated unless a column involved in the index changes) is an optimization, Heap-Only Tuples. It's a very common optimization, but it's not guaranteed. -- -- Christophe Pettus x...@thebuild.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Avoid using index scan backward when limit order desc
s (cost=0.43..344332.66 rows=2061 width=187) (actual
time=60133.695..60133.695 rows=0 loops=1)
Filter: (((type)::text = ANY ('{foo,bar}'::text[])) AND (bucket_id
= ANY ('{30231,30230,30104}'::integer[])))
Rows Removed by Filter: 3441510
Planning time: 1.034 ms
Execution time: 60133.740 ms
** With limit 50 **
EXPLAIN ANALYZE SELECT * FROM notifications WHERE bucket_id IN
(30231,30230,30104) AND type IN ('foo', 'bar') ORDER BY created_at DESC
limit 50;
--
Limit (cost=7429.94..7430.06 rows=50 width=187) (actual time=0.111..0.111
rows=0 loops=1)
-> Sort (cost=7429.94..7435.09 rows=2061 width=187) (actual
time=0.110..0.110 rows=0 loops=1)
Sort Key: created_at
Sort Method: quicksort Memory: 25kB
-> Bitmap Heap Scan on notifications (cost=71.68..7361.47
rows=2061 width=187) (actual time=0.107..0.107 rows=0 loops=1)
Recheck Cond: (((type)::text = ANY ('{foo,bar}'::text[]))
AND (bucket_id = ANY ('{30231,30230,30104}'::integer[])))
-> Bitmap Index Scan on
index_notifications_on_type_and_bucket_id
(cost=0.00..71.16 rows=2061 width=0) (actual time=0.105..0.105 rows=0
loops=1)
Index Cond: (((type)::text = ANY
('{foo,bar}'::text[])) AND (bucket_id = ANY ('{30231,30230,30104}'::intege
r[])))
Planning time: 0.151 ms
Execution time: 0.139 ms
As you can see, when I have the LIMIT 20, the execution time takes around 1
minutes (on a very small subset of the entire table).
Actually I have tried different LIMIT, and when the LIMIT is <= 45, it will
use the index scan backward.
Removing the index 'index_notifications_on_created_at_and_bucket_id' may
prevent the planner from choosing the index scan backward for this query,
but this index is used for other querying on that table...
1) Why is the planner changing index scanning at the threshold of 45 for
the LIMIT ? Why not 50 ? 100 ? I may take the solution in my application to
have a LIMIT > 45 in order to prevent the performance issue, but am I sure
that this threshold will always be the same ?
2) Is it possible for a specific query to force the planner on choosing a
given index or preventing it from choosing one ?
What kind of other options do I have to solve this performance issue ?
Thanks in advance for any help,
Regards,
--
Christophe Escobar
[GENERAL] AccessExclusiveLock on tuple?
On 9.4, I've encountered a locking message I've not seen before: process 5293 still waiting for AccessExclusiveLock on tuple (88636,15) of relation 18238 of database 16415 after 5000.045 ms What conditions produce an "AccessExclusiveLock on tuple"? Attempting to lock a tuple when another process has done an explicit LOCK ACCESS EXCLUSIVE? -- -- Christophe Pettus x...@thebuild.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] AccessExclusiveLock on tuple?
On Dec 2, 2015, at 9:25 AM, Bill Moran wrote: > No. See the section on row level locks here: > http://www.postgresql.org/docs/9.4/static/explicit-locking.html That wasn't quite my question. I'm familiar with the row-level locking and the locking messages in general, but this message implies there is such a thing as an AccessExclusiveLock on a tuple, which is new to me. I wasn't able to produce this message experimentally doing various combinations of UPDATE statements and SELECT FOR UPDATEs, or even with explicit LOCK ACCESS EXCLUSIVE MODE, thus the question. -- -- Christophe Pettus x...@thebuild.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] AccessExclusiveLock on tuple?
On Dec 2, 2015, at 9:50 AM, Bill Moran wrote: > Then wait for a little while and the message will be logged. Well, yes and no :) : 2015-12-02 10:07:40.281 PST,"xof","xof",8465,"[local]",565f3365.2111,4,"UPDATE waiting",2015-12-02 10:07:33 PST,3/12,156,LOG,0,"process 8465 still waiting for ShareLock on transaction 155 after 1001.203 ms","Process holding the lock: 8428. Wait queue: 8465.""while updating tuple (0,1) in relation ""x""","update x set a='a';",,,"psql" Note that it's waiting for a ShareLock, not an AccessExclusiveLock, thus my question. Just to clarify, my very specific question is about "AccessExclusiveLock". -- -- Christophe Pettus x...@thebuild.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] AccessExclusiveLock on tuple?
On Dec 2, 2015, at 10:29 AM, Tom Lane wrote: > The short answer is that heavyweight tuple locks can be taken internally > by UPDATE, DELETE, and other row-level operations, and the specifics of > which type of lock any given action takes are implementation details that > can change from time to time. Great, thank you! -- -- Christophe Pettus x...@thebuild.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] CoC [Final v2]
On Jan 22, 2016, at 6:47 PM, Joshua D. Drake wrote: > == PostgreSQL Community Code of Conduct (CoC) == What is missing from this, first and foremost, is a reporting and resolution mechanism. If someone feels the CoC has been violated, who do they talk to? How does that person or entity resolve things? What confidentiality promises are made? -- -- Christophe Pettus x...@thebuild.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] CoC [Final v2]
On Jan 24, 2016, at 2:48 PM, "David E. Wheeler" wrote: > I think that’s planned for a separate document, to be linked. I think those need to put in place at the same time. It's very hard to judge how good or bad a CoC is absent a reporting mechanism. I'd respectfully suggest that we table the discussion of the CoC text at this point, let the high passions moderate a bit, and talk about the process. That is the detail in which the devils will live. -- -- Christophe Pettus x...@thebuild.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] CoC [Final v2]
On Jan 24, 2016, at 5:15 PM, "Joshua D. Drake" wrote: > Based on our structure it doesn't work that way. At a minimum we will come up > with a CoC and it will be passed to -core for final approval. -core will then > also define how they want implement it (or even turn us down). We are just > doing some of the hard work for them so that they see what the community and > majority of contributors come up with. I think that it is the understatement of the year (to date) to say that consensus is not emerging here. Worse yet, it is causing huge rifts in the community while not resulting in an agreed-to product. I am pro-CoC, but without a documented enforcement and resolution mechanism, we might as well just add "be excellent to each other" on postgresql.org and be done with it. I'd suggest that -core take over from this point, and decide on a full package, rather than continuing this process here in -general. -- -- Christophe Pettus x...@thebuild.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] CoC [Final v2]
On Jan 24, 2016, at 5:35 PM, Joshua D. Drake wrote: > You are wrong and the fact that we have gone from a motion style, to a story > style, to a continually and incrementally improving draft proves it. This is > the largest feature the community has tried to design and implement. It is > going to take a little time. "The document has changed" is not a consensus emerging. A significant portion of the participants don't want a CoC at all, and they are feeling ignored. A significant portion of the participants don't want this CoC, because they feel it's not strong enough, and they're feeling ignored. Those two parties are not trivial; in fact, they make up most of the people who are commenting right now. > This shouldn't cause any rifts. And, yet, it is. > If you don't want to constructively participate in the development of this > feature, nobody is asking you to. Participation does not need to be limited to copy-editing. Of all the ways to develop a community CoC, we're engaged in just about the worst possible one right now. -- -- Christophe Pettus x...@thebuild.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] CoC [Final v2]
On Jan 24, 2016, at 6:09 PM, John R Pierce wrote: > so what would be a better way of developing this ? This needs to come from -core, and then commented on as a complete policy, not just CoC with maybe enforcement provisions later. Not because we're a dictatorship, but if they are going to be the ones responsible for handling complaints, they need to be 100% bought into it. A CoC with no enforcement mechanism is pointless. If there's no mandate from -core to have a CoC, this is just pantomime. Let's say I arrive a -general with a proposal that PG 9.7 should speak the MongoDB wire protocol in addition to v3, complete with some working code. The comments on -general come down to: 1. A large number of people saying I am insane. 2. A smaller number of people saying, "Yes, but which version?" 3. A large number of people saying, "No, it should speak MySQL's protocol instead." I can't claim that, on the basis of #2, there's "consensus" that the feature is a good idea and should be refined and committed, but that's precisely what I see happening here. In any event, the tone of this particular discussion has gotten so out of control (basically, people are being told to shut up left and right), that I don't see a consensus is possible right now. -- -- Christophe Pettus x...@thebuild.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] ERROR: check constraint - PostgreSQL 9.2
On Jan 24, 2016, at 8:12 PM, "drum.lu...@gmail.com" wrote: > How can I solve the problem? How can I get the command successfully be done? Two options: 1. Fix the data. 2. Use the NOT VALID option on ALTER TABLE ... ADD constraint, which allows the addition of a constraint without actually checking its validity. -- -- Christophe Pettus x...@thebuild.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] ERROR: check constraint - PostgreSQL 9.2
On Jan 24, 2016, at 8:17 PM, Christophe Pettus wrote: > 2. Use the NOT VALID option on ALTER TABLE ... ADD constraint, which allows > the addition of a constraint without actually checking its validity. And note that you might miss some potential planner optimizations this way, as the planner will not assume the constraint holds true unless you remove the NOT VALID condition from it with VALIDATE CONSTRAINT. -- -- Christophe Pettus x...@thebuild.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] ERROR: check constraint - PostgreSQL 9.2
On Jan 24, 2016, at 9:01 PM, Charles Clavadetscher wrote: > What is the point of having a check constraint that is not checked? Well, it *is* checked going into the future; it's just not checked at the time the constraint is added. Ultimately, you do want to fix the data, but this makes it a two-step process, and reduces the time the table is locked against access. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] pg_upgrade with an extension name change
I'm attempting to upgrade a database from 9.2 to 9.5 using pg_upgrade. The 9.2 database has the "orafunc" extension installed, which appears to have changed names to "orafce". pg_upgrade complains that it can't find "orafunc" on 9.5, which is true. Is there a standard way of handling this situation? -- -- Christophe Pettus x...@thebuild.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] ERROR: MultiXactId xxx has not been created yet
We have a database (PostgreSQL 9.3.10) which is reporting this error on a TOAST table on a VACUUM. Is there a canonical way of repairing this? The table is *huge*, so a VACUUM FULL or pg_dump / pg_restore is probably not going to work. -- -- Christophe Pettus x...@thebuild.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Members in the Middle East?
On May 24, 2016, at 1:16 PM, Gavin Flower wrote: > What does 'GCC' stand for? Gulf Cooperative Council. :) https://en.wikipedia.org/wiki/Gulf_Cooperation_Council -- -- Christophe Pettus x...@thebuild.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] how can i bugfix "idle in transaction" lockups ?
On Nov 30, 2010, at 7:21 AM, Jonathan Vanasco wrote: > i couldn't find anything in the docs, and the project has decent traffic, so > its going to be an issue to log all statements so I can sift through the data > by PID With machine-readable logs, it shouldn't be *that* serious an issue. 1. Are you using connection pooling? 2. What's the application server environment? -- -- Christophe Pettus x...@thebuild.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Partitioned Database and Choosing Subtables
On Mar 14, 2011, at 8:30 PM, Bill Thoen wrote: > I've got a ver 8.4.5 partitioned data base with records organized by US > state, so the partitions are set up by state. When I query this database and > include the key field that tells postgres what partition you , everything > works as I expect. It searches only the specified partition, and it's fast . > But that's only if I use a constant, like this: > > SELECT lions, tigers, bears FROM WildLife > WHERE state_pt = 'CO'; > > What I want to be able to do is put this key value in a table and PG look in > whatever partition the column specifies, like so: > > SELECT lions, tigers, bears, statecode FROM WildLife > WHERE state_pt = statecode; > > However when I try anything other than a constant, it search EVERY partition, > sequentially, which is not what I want it to do. So is there any way to > specify the partition to search using a variable/column name? To answer the specific question you ask, you can always tell Postgres to search a particular child table: SELECT lions, tigers, bears FROM Wildlife_CA ... But your example seems somewhat unclear to me. Are both "state_pt" and "statecode" columns in Wildlife? If so, Postgres is going to have to search every partition, because it can't just from the partition constraint know which entries will match and which do not until it looks inside every record. Or did you mean 'statecode' to be a column in a different table, on which you're joining? -- -- Christophe Pettus x...@thebuild.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Retrieving the role in a logical replication plugin
I'm working on a 9.4 logical replication plugin, mostly for my own edification, and have run into a limit of my knowledge: How can I retrieve the role (either oid or textual name) associated with the operations my callbacks are receiving? Apologies if it is staring me in the face and I've missed it... -- -- Christophe Pettus x...@thebuild.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] In need of some JSONB examples ?
On Jan 23, 2015, at 7:40 AM, Tim Smith wrote:
> re: (a)
>
>> see the documentation pertaining to 'jsonb indexing', to wit:
>>
>> -- Find documents in which the key "company" has value "Magnafone"
>> SELECT jdoc->'guid', jdoc->'name' FROM api WHERE jdoc @> '{"company":
>> "Magnafone"}';
>
> Nope, sorry, tried that. Doesn't work for me. Hence the question. ;-)
The problem is that @> only operates at the top level of the JSON object
presented to it:
xof=# TABLE j;
f
[{"a": 1, "b": 2}]
{"a": 1, "b": 2}
(2 rows)
xof=# SELECT * FROM j WHERE f @> $$ { "a": 1 } $$::jsonb;;
f
--
{"a": 1, "b": 2}
(1 row)
I'm actually not seeing a great solution to your particular problem. If you
know for sure that everything always has the format you describe, you can use
jsonb_array_elements to extract the individual members of the array, and use @>
on them, via a JOIN, but it's not clear that an index will help you there.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] In need of some JSONB examples ?
On Jan 23, 2015, at 12:20 PM, Tim Smith wrote: > So basically we're saying JSON in 9.4 is still a little way from where > it needs to be in terms of real-world functionality ? Or am I being > too harsh ? ;-) "Doesn't meet my particular use-case exactly" is not quite the same thing. -- -- Christophe Pettus x...@thebuild.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Logical decoding output plug-in questions
I'm having a great time playing with logical decoding, and had a couple of parameter questions: * OutputPluginPrepareWrite, OutputPluginWrite -- last_write. This is false if you are planning to do another OutputPluginPrepareWrite/OutputPluginWrite pair in the same invocation of the same callback function, yes? * On an UPDATE, it appears that data.tp.oldtuple is only not-NULL if a primary key field changed, and then it only includes primary key fields (the rest being NULL). Correct? * pg_decode_startup -- is_init. What's the situation under which this is true or false? Thanks! -- -- Christophe Pettus x...@thebuild.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Logical decoding output plug-in questions
Thank you! On Jan 26, 2015, at 2:24 PM, Andres Freund wrote: > What's in the old tuple depends on the configured replica identity. My apologies, but... "configured replica identity"? > The is_init parameter will be true when the > replication slot is being created [...] Thanks! I suppose my question then is: Besides slot creation, when is pg_decode_startup called? -- -- Christophe Pettus x...@thebuild.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to use daterange type?
On Mar 16, 2013, at 2:05 PM, Csanyi Pal wrote:
> So how can I use this table further eg. to get dates of the school days
> but without Saturdays and Sundays?
You can't do that directly (that kind of calendar operation is outside of the
scope of a range type). You can, however, easily write selects that handle
that:
postgres=# SELECT count(*)
postgres-#FROM generate_series(lower('[2012-09-01,
2012-12-24]'::daterange)::timestamp, upper('[2012-09-01,
2012-12-24]'::daterange)::timestamp, '1 day') as day
postgres-#WHERE EXTRACT(dow FROM day) BETWEEN 1 AND 5;
count
---
82
(1 row)
In cases where you have more complex calendars (like lists of bank holidays),
you could join against a table of them, or use a function that determines
whether or not a particular day is holiday or not.
--
-- Christophe Pettus
x...@thebuild.com
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
