| kemo | hi |
|---|
| bluelaguna | ??unix |
|---|
| pg_docbot_adz | For information about 'unix' see: |
|---|
| pg_docbot_adz | http://www.postgresql.org/docs/current/static/functions-datetime.html#FUNCTIONS-DATETIME-EXTRACT |
|---|
| kaveh | Hi everyone, I am searching (lamely) in google for a while, can anyone tell me when I have a STATEMENT based trigger how can I get the current statement? |
|---|
| kaveh | Like the NEW parameter in ROW baed triggers |
|---|
| RhodiumToad | no |
|---|
| StuckMojo | hehe, "no, we won't tell you" |
|---|
| StuckMojo | i think he means "no, you can't" |
|---|
| copumpkin | if I had a src, dst table representing an arbitrary graph and wanted to fetch all incoming and outgoing edges from a given node in a single query, could a CTE help me there? |
|---|
| copumpkin | where src and dst are just ints representing nodes (and a row is an edge) |
|---|
| RhodiumToad | you don't need a cte for that |
|---|
| copumpkin | well, I wanted to go a level deeper after that |
|---|
| RhodiumToad | UNION is your friend |
|---|
| kaveh | :) Thanks a lot, so it seems it is impossible |
|---|
| RhodiumToad | just one level, or many? |
|---|
| copumpkin | the more general thing I want is to fetch (not recursively): the queried node, all nodes incoming and outgoing from it; all incoming nodes for the nodes discovered from queried nodes outgoings, and vice versa |
|---|
| copumpkin | kind of hard to explain |
|---|
| copumpkin | a bit like http://en.wikipedia.org/wiki/Markov_blanket, except I also want the outgoings of my incomings |
|---|
| Kenaniah | ??replication |
|---|
| pg_docbot_adz | For information about 'replication' see: |
|---|
| pg_docbot_adz | http://slony.info/ :: http://www.postgres-r.org :: http://www.commandprompt.com/products/mammothreplicator/ :: http://bucardo.org/ :: https://projects.commandprompt.com/public/replicator :: http://wiki.postgresql.org/wiki/Streaming_Replication |
|---|
| pg_docbot_adz | http://momjian.us/main/writings/pgsql/replication.pdf :: http://www.postgresql.org/docs/current/static/high-availability.html :: http://pgfoundry.org/projects/skytools/ :: http://www.pgcluster.org |
|---|
| copumpkin | is that something I can do with a CTE, or is it something I should still do with some subqueries and some unions? |
|---|
| RhodiumToad | you can do literally anything with a CTE, the only question is whether you're better off with static unions |
|---|
| RhodiumToad | (CTEs are turing-complete) |
|---|
| copumpkin | I figured I might get more sharing with a CTE |
|---|
| copumpkin | as I need to both retrieve the direct neighbors and use them in a query to find their neighbors |
|---|
| RhodiumToad | copumpkin: that may or may not make up for the overhead |
|---|
| Kenaniah | thoughts on bucardo anyone? |
|---|
| s34n | I'd like to install a pg Windows client |
|---|
| s34n | but the downloads seem to be for the whole enchilada |
|---|
| copumpkin | RhodiumToad: hmm |
|---|
| s34n | is there a minimal windows client install somewhere? |
|---|
| s34n | just enough to let me connect to a server on another machine? |
|---|
| StuckMojo | pgadmin? |
|---|
| Snow-Man | sadly, I don't think so |
|---|
| Snow-Man | yea, you could grab pgadmin, if that's the interface you want |
|---|
| Snow-Man | if you want actual psql, you have to pull out the bits you need from the big package that edb provides, iirc |
|---|
| StuckMojo | i imagine the pgadmin3 installer comes with libpq, if not you could grab the pg odbc driver and use any odbc enabled client |
|---|
| s34n | well, not exactly |
|---|
| Snow-Man | yes, pgadmin3 comes with libpq, iirc |
|---|
| s34n | StuckMojo: right idea though |
|---|
| Snow-Man | s34n: what client do you want? |
|---|
| Snow-Man | that's probably the first question.. |
|---|
| s34n | StuckMojo: I'm trying to connect from AutoCAD :( |
|---|
| StuckMojo | oh, go odbc then |
|---|
| Snow-Man | what does autocad support? odbc? |
|---|
| s34n | which thought it would invent it's own FDO |
|---|
| s34n | and the FDO provider for postgis doesn't seem to actually include the dlls for connecting |
|---|
|
|
| kemo | have to load ~100 GB into 5 different dbms an record which performs better in terms of speed (sec). is ther some kind of "best practise" or a super special universally usable bulk loading tool? |
|---|
| Snow-Man | s34n: that's kind of curious. My guess is that it needs libpq... |
|---|
| StuckMojo | IME almost anything windows can talk odbc |
|---|
| Action: Snow-Man doesn't know much about FDO |
| Snow-Man | kemo: how about "pick a database based on your actual requirements rather than some arbitrary metric" |
|---|
| breinbaas | kemo: most dbms have their own superspecialised bulkloader |
|---|
| s34n | Snow-Man: yes. it does |
|---|
| breinbaas | the comparison can be interesting in itself, if you can put enough time into tuning each system |
|---|
| s34n | Snow-Man: more curiouser, it also needs MSVCR90.dll |
|---|
| breinbaas | kemo: which systems? |
|---|
| kemo | win server 2003 |
|---|
| Snow-Man | indeed.. PG has COPY and pg_restore, Oracle has imp, Mysql has 'load data'... |
|---|
| breinbaas | sybase, bcp |
|---|
| StuckMojo | s34n: that would mean it expects that your libpq was built with VC, which is not surprising |
|---|
| Snow-Man | StuckMojo: odd that they don't distribute it |
|---|
| StuckMojo | s34n: the 'R' is redistributable, so you can get that dll easily |
|---|
| Snow-Man | I would guess it comes w/ pgadmin3 tho.. |
|---|
| s34n | I should be able to grab MSVCR90.dll and IESHIMS.dll |
|---|
| s34n | I just scratch my head that they aren't included in their silly provider to begin with |
|---|
| Snow-Man | they're probably expecting that you've installed the whole PG system |
|---|
| Snow-Man | and don't want to have duplicate/possibly different versions, running around |
|---|
| StuckMojo | that or they didn't realize they were linked to it because it was down the chain or something |
|---|
| StuckMojo | it's easy not to realize |
|---|
| Snow-Man | sadly, windows sucks, and doesn't have the kind of nice packaging system that real systems, like Debian, do |
|---|
| StuckMojo | most machines you test on have VC installed, and thus already have it |
|---|
| StuckMojo | so you don't realize you need it until you're at a client site doing an install ;) |
|---|
| StuckMojo | PITA |
|---|
| s34n | If only I could replace AutoCAD with some nice Unix-based solution... |
|---|
| StuckMojo | blender? |
|---|
| StuckMojo | heh |
|---|
| Snow-Man | blender is pretty cool |
|---|
| Action: s34n chuckles |
| s34n | blender gis would be interesting |
|---|
| Snow-Man | autocad does gis? |
|---|
| Snow-Man | been a long time, but I don't remember lat/longs in autocad. :) |
|---|
| breinbaas | can someone clean this up? http://pgsql.privatepaste.com/7848a3cc44 (xlog monitoring SQL) |
|---|
| breinbaas | there is just no way to get info from the master db, is there? |
|---|
| Snow-Man | huh, autocad map 3d does look kinda neat |
|---|
| perry81 | yes |
|---|
| Snow-Man | breinbaas: might be cleaner as a pl/pgsql function.. |
|---|
| breinbaas | yes, that's probably better |
|---|
| quentusrex_ | I'm trying to drop a database, but there are still connections |
|---|
| quentusrex_ | how do I disconnect all sessions and drop the database? |
|---|
| quentusrex_ | I'm trying to restore from a backup... |
|---|
| ads | quentusrex_: stop all applications |
|---|
| ads | quentusrex_: including your own connection. |
|---|
| ads | quentusrex_: you can connect to another database (like "postgres" or "template1") |
|---|
| sfuentes | anyone how i can get the status result of an EXECUTE statement in a stored procedure? |
|---|
| quentusrex_ | ads, how do I force it off? |
|---|
| quentusrex_ | I can't find the one remote user who has his pgadmin3 still connected. |
|---|
| ads | select * from pg_stat_activity; |
|---|
| quentusrex_ | and how do I force off the connections? |
|---|
| ads | shutdown your db, modify pg_hba.conf and reject all new connections for this database. |
|---|
| Kenaniah | quentusrex: if your server consists of only one database, you can "pg_ctl stop -m immediate", create your recovery file, and start |
|---|
| codeaholic | quabbin_: you can always kill their postgres process |
|---|
| codeaholic | look for the pid from pg_stat_activity and kill -9 it |
|---|
| ads | codeaholic: NO |
|---|
| ads | Never ever kill -9 a postmaster |
|---|
| ads | -1 is enough |
|---|
| codeaholic | okay HUP it |
|---|
| Kenaniah | doesn't hup just tell it to re-ready config? |
|---|
| Kenaniah | *re-read the config? |
|---|
| ads | You risk a corrupt shared memory |
|---|
| codeaholic | ah |
|---|
| ads | Kenaniah: not for the single postmaster prozess |
|---|
| codeaholic | Kenaniah: that's just by convention. a process can do anything it wants in the HUP signal handler |
|---|
| ads | codeaholic: please don't give such advises if you don't know what your advise is doing! |
|---|
| codeaholic | ads: we all make mistakes. even you |
|---|
| Kenaniah | codeaholic: that was a pretty catastrophic mistake =P |
|---|
| codeaholic | chill out. you were here to save the data. contrats captain awesome |
|---|
| ads | codeaholic: yeah, even me. |
|---|
| codeaholic | My _POINT_ was that you can kill the process associated with his connection and disconnect him |
|---|
| ads | sure - if you kill -9 it, you can even end up with having the database closing all connections and doing a recovery. |
|---|
| copumpkin | this looks pretty hardcore: http://pastie.org/817418 |
|---|
| copumpkin | anyone have any suggestions? |
|---|
| endpoint_david | copumpkin: describe what your query is attempting to accomplish |
|---|
| copumpkin | endpoint_david: for a given node, get all edges involving: its successors, its predecessors, its successors' predecessors, and its predecessors' successors |
|---|
| BlueAidan_work | ??tweakers |
|---|
| pg_docbot_adz | For information about 'tweakers' see: |
|---|
| pg_docbot_adz | http://tweakers.net/reviews/649/7 |
|---|
| pg_docbot_adz | http://tweakers.net/reviews/661/7 |
|---|
| copumpkin | there are no seq scans, which makes me happy |
|---|
| copumpkin | but I'm not sure if it's as efficient as it can be |
|---|
| copumpkin | (it's going to be running on a huge table) |
|---|
| oranges | ok raid 10 for db |
|---|
| oranges | now how bad will it be to set that up under linux |
|---|
| oranges | like software raid |
|---|
| copumpkin | lines 36,37 and 41,42 seem identical... is that shared? |
|---|
| oranges | I haven't done 10 |
|---|
| copumpkin | how bad would raid 6 be for a database? |
|---|
| oranges | I was told raid 10 is bestest |
|---|
| oranges | raid 5 bad write speed |
|---|
| copumpkin | yeah |
|---|
| copumpkin | makes sense |
|---|
| oranges | I love big women. |
|---|
| copumpkin | good to know. |
|---|
| oranges | postgresql seems to be able to handle some serious heavy lifting |
|---|
| oranges | I am guna install it tomorrow |
|---|
| jay321 | folks is it possible to have a private/local and public schema, where one table exists in both spaces, as a local version, then public version? in oracle, you can have a user$foo table, then a public$foo table as well. all sql acts on the user$foo by default. |
|---|
| endpoint_david | jay321: yes |
|---|
| endpoint_david | the resolution is determined by the search_path, which is usually $user,public |
|---|
| jay321 | endpoint_david, ok thank you for your quick response, what is this set up referred to in the docs? |
|---|
| endpoint_david | ??schema |
|---|
| pg_docbot_adz | For information about 'schema' see: |
|---|
| pg_docbot_adz | http://www.postgresql.org/docs/current/static/sql-dropschema.html :: http://www.postgresql.org/docs/current/static/information-schema.html :: http://www.postgresql.org/docs/current/static/ddl-schemas.html |
|---|
| pg_docbot_adz | http://www.postgresql.org/docs/current/static/sql-createschema.html :: http://www.postgresql.org/docs/current/static/ddl-schemas.html#DDL-SCHEMAS-CREATE :: http://www.postgresql.org/docs/current/static/sql-alterschema.html |
|---|
| pg_docbot_adz | http://www.postgresql.org/docs/current/static/sql-expressions.html#AEN1693 |
|---|
| endpoint_david | ??search_path |
|---|
| pg_docbot_adz | For information about 'search_path' see: |
|---|
| pg_docbot_adz | http://www.postgresql.org/docs/current/static/ddl-schemas.html#DDL-SCHEMAS-PATH |
|---|
| jay321 | pretty cool, thank you again :D |
|---|
| endpoint_david | probably that last one will be most useful |
|---|
| neruda_ | when i run 'postgres -D $somevar >logfile 2>&1 &' I get an error in logfile that says: FATAL: unrecognized configuration parameter "max_fsm_pages" |
|---|
| neruda_ | any thoughts? |
|---|
| neruda_ | anyone awake in here |
|---|
| Snow-Man | perhaps |
|---|
| Snow-Man | I don't typically run postgres that way, heh. |
|---|
| path | #define awake.h |
|---|
| Snow-Man | looks like you're using a version which doesn't have max_fsm_pages as a config option in any case though |
|---|
| Snow-Man | I hope you're not trying to do an in-place upgrade between major versions, that's not typically supported.. You can do it with an add-on utility between 8.3 and 8.4, but that's about it... |
|---|
| jmoiron | hi all; what determines the unix socket that postgresql runs on? something seemingly switched it up on me (maybe an upgrade) and i ran into trouble for a bit |
|---|
| jmoiron | solved (for now?) but would like to know for the future; it appears mine is named .s.PGSQL.5433 now, but my app was expecting 5432 |
|---|
| Snow-Man | are you running Debian? |
|---|
| Snow-Man | Try checking pg_lsclusters ? |
|---|
| jmoiron | it says 5432 is down.. |
|---|
| Snow-Man | basically, the # in the unix socket name is derived from the port number |
|---|
| jmoiron | not quite; yeah, i figured that, since 5432 is the default port |
|---|
| Snow-Man | perhaps you did a major-version upgrade and the old cluster is still configured on the default port..? |
|---|
| jmoiron | perhaps; i thought i had done so a while ago.. it says the main cluster is down |
|---|
| jmoiron | but i have one running that pg_lsclusters doesn't list |
|---|
| jmoiron | or.. i have something running, on that alternate port, that appears to be functional; and other data directories |
|---|
| Snow-Man | sounds kinda messy |
|---|
| Snow-Man | did you install from source or something? |
|---|
| jmoiron | it's not anything close to mission critical just wondering how things went pear.. no, I just don't recall an upgrade recently other than one from 8.3 -> 8.4 |
|---|
| jmoiron | which are completely separated on debian |
|---|
| Snow-Man | erm, they'd both show up in pg_lsclusters ... |
|---|
| Snow-Man | if you used the Debian packages and whatnot, which I would recommend |
|---|
| Snow-Man | beren:/home/sfrost> pg_lsclusters |
|---|
| Snow-Man | Version Cluster Port Status Owner Data directory Log file |
|---|
| jmoiron | i did |
|---|
| Snow-Man | 8.3 main 5432 online postgres /var/lib/postgresql/8.3/main /var/log/postgresql/postgresql-8.3-main.log |
|---|
| Snow-Man | 8.4 main 5433 online postgres /var/lib/postgresql/8.4/main /var/log/postgresql/postgresql-8.4-main.log |
|---|
| Snow-Man | heh. |
|---|
| Snow-Man | we're still working on our migration from 8.3 to 8,4. :) |
|---|
| jmoiron | i don't see that; i see this |
|---|
| jmoiron | well |
|---|
| jmoiron | the same as your second line |
|---|
| jmoiron | except the status is 'down' |
|---|
| Snow-Man | hrmpf. |
|---|
| commx | i'm a bit confused about how pgsql deals with integers. do I have to use int8 to store unsigned integers > 2 billion? |
|---|
Popular searches: 
