Re: [GENERAL] remote tcp connection problem PG 8.0.1
i'm having the same problem running pg8.0.1 on redhat enterprise linux AS 3 (2.4.21-27.0.4.ELsmp). shutting off my firewall via 'service iptables stop' solves the problem... what needs to be modified in the iptables to allow remote pg connections? Richard Huxton wrote: Michael Korotun wrote: Hi Guys, I can't get working remote tcp connections on default port 5432. Environment is as follows OS: Fedora Core 3 DB: Postgresql 8.0.1 1) The listen_addresses is set to '*' in postgresql.conf 2) pg_hba.conf is edited with client host ip, (the one which tries to eastablish connection) 1. Turn connection logging on in your postgresql.conf and restart postgresql. 2. From the database server, try "telnet localhost 5432" - it should connect (and display nothing). Type "A" and hit return twice - you should be disconnected. Check your logs and there should be a message about an invalid startup packet. 3. Do the same from your client, "telnet DB-SERVER-IP-ADDRESS 5432" and repeat the test. If step 3 works, then there's no problem with the server. If you can't connect at step 3 and PG doesn't log anything then you probably have a firewall in the way. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[GENERAL] inherit with foreign key reference
dear readers, i've created a simple Fooey table that inherits from Foo: !--! CREATE TABLE Foo( fooid serial UNIQUE, footype text); CREATE TABLE Fooey( datatext); INHERITS(Foo); !--! next i try to create a Bar table that references Fooey's fooid (inherited from Foo) as a foreign key: !--! CREATE TABLE Bar( fooeyid int REFERENCES Fooey(fooid)); !--! unfortunately i get the following error: !--! ERROR: there is no unique constraint matching given keys for referenced table "pagesrc" !--! thoughts? thanks, aaron ---(end of broadcast)--- TIP 3: 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] lo_import()
hi,
i would like to use lo_import() on the client side to insert images
into postgresql version 8.0.1 on a remote server using psql as follows:
psql -h my.db.host.com -U user -c "insert into binblob (binary_blob)
values(lo_import('/path/on/client/machine/ks.jpg'))" -d mydb
i'm getting the following error:
ERROR: must be superuser to use server-side lo_import()
HINT: Anyone can use the client-side lo_import() provided by libpq.
isn't the above command the client-side lo_import()? thoughts?
thanks,
cb
[GENERAL] warning: pg_query(): Query failed
hi, i'm setting up a forum that's connected to postgresql 7.4.2 (via drupal.org framework) and i'm getting some errors. here's the link, followed by the errors verbatim: http://128.32.146.140/dmap/?q=forum warning: pg_query(): Query failed: ERROR: invalid input syntax for type boolean: "2" CONTEXT: PL/pgSQL function "if" line 2 at if in /Library/WebServer/Documents/dmap/includes/database.pgsql.inc on line 104. user error: query: SELECT DISTINCT(n.nid), l.last_comment_timestamp, IF(l.last_comment_uid, cu.name, l.last_comment_name) as last_comment_name, l.last_comment_uid FROM node n , node_comment_statistics l /*! USE INDEX (node_comment_timestamp) */, users cu, term_node r WHERE n.nid = r.nid AND r.tid = 1 AND n.status = 1 AND n.type = 'forum' AND l.last_comment_uid = cu.uid AND n.nid = l.nid AND '1' ORDER BY l.last_comment_timestamp DESC LIMIT 1 OFFSET 0 in /Library/WebServer/Documents/dmap/includes/database.pgsql.inc on line 121. any ideas? here's my system: - drupal v4.5.1 - mac os x server v10.3.5 - postgresql v7.4.2 - php v4.3.6 thanks, aaron ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] warning: pg_query(): Query failed
hi bruno, would it be useful to see the IF PLSQL function, or would you recommend a modification to the database.pgsql.inc file? On Jan 5, 2005, at 10:52 AM, Bruno Wolff III wrote: On Wed, Jan 05, 2005 at 10:17:39 -0800, Aaron Steele <[EMAIL PROTECTED]> wrote: hi, warning: pg_query(): Query failed: ERROR: invalid input syntax for type boolean: "2" CONTEXT: PL/pgSQL function "if" line 2 at if in /Library/WebServer/Documents/dmap/includes/database.pgsql.inc on line 104. any ideas? The actual problem is in the PLSQL function named IF that you didn't show us. Recent versions of postgres have tightened up casting to boolean. Perhaps in the past '2'::boolean might have worked, but in 7.4 this won't work. '1' or '0' are valid boolean representations. ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] warning: pg_query(): Query failed
hi steven,
here's another file where the actual SQL query (the one in the error
message) is defined. it's in the forum_get_forums() function. bruno
noticed that the IF (via pl/pgsql) might not like the boolean casting
to "2" since 7.4 is more strict about casting? does this help?
/**
* @file
* Enable threaded discussions about general topics.
*/
/**
* Implementation of hook_help().
*/
function forum_help($section) {
switch ($section) {
case 'admin/help#forum':
return t("
Creating a forum
The forum module uses taxonomy to organize itself. To create a
forum you first have to create a taxonomy
vocabulary. When doing this, choose a sensible name for it (such as
\"fora\") and make sure under \"Types\" that \"forum\" is selected.
Once you have done this, add some terms to
it. Each term will become a forum. If you fill in the description
field, users will be given additional information about the forum on
the main forum page. For example: \"troubleshooting\" - \"Please ask
your questions here.\"
When you are happy with your vocabulary, go to administer » settings » forum and set
Forum vocabulary to the one you have just created.
There will now be fora active on the site. For users to access them
they must have the \"access content\" permission and to create a topic they must
have the \"create forum topics\" permission. These permissions can be set in
the permission pages.
Icons
To disable icons, set the icon path as blank in administer » settings » forum.
All files in the icon directory are assumed to be images. You
may use images of whatever size you wish, but it is recommended to use
15x15 or 16x16.", array("%taxonomy" =>
url('admin/taxonomy/add/vocabulary'), '%taxo-terms' =>
url('admin/taxonomy'), '%forums' => url('admin/settings/forum'),
'%permission' => url('admin/user/configure/permission')));
case 'admin/modules#description':
return t('Enable threaded discussions about general topics.');
case 'admin/settings/forum':
return t("Forums are threaded discussions based on the taxonomy
system. For the forums to work, the taxonomy module has to be
installed and enabled. When activated, a taxonomy vocabulary (eg.
\"forums\") needs to be created and bound to
the node type \"forum topic\".", array('%created' =>
url('admin/taxonomy/add/vocabulary')));
case 'node/add#forum':
return t('A forum is a threaded discussion, enabling users to
communicate about a particular topic.');
}
// The 'add forum topic' form takes a variable argument:
if (substr($section, 0, 14) == 'node/add/forum'){
return variable_get('forum_help', '');
}
}
/**
* Implementation of hook_node_name().
*/
function forum_node_name($node) {
return t('forum topic');
}
/**
* Implementation of hook_access().
*/
function forum_access($op, $node) {
if ($op == 'create') {
return user_access('create forum topics');
}
}
/**
* Implementation of hook_perm().
*/
function forum_perm() {
return array('create forum topics');
}
/**
* Implementation of hook_settings().
*/
function forum_settings() {
if (module_exist('taxonomy')) {
$vocs[0] = '<'. t('none') .'>';
foreach (taxonomy_get_vocabularies('forum') as $vid => $voc) {
$vocs[$vid] = $voc->name;
}
if ($voc) {
$group = form_select(t('Forum vocabulary'),
'forum_nav_vocabulary', variable_get('forum_nav_vocabulary', ''),
$vocs, t("The taxonomy vocabulary that will be used as the navigation
tree. The vocabulary's terms define the forums."));
$group .= _taxonomy_term_select(t('Containers'),
'forum_containers', variable_get('forum_containers', array()),
variable_get('forum_nav_vocabulary', ''), t('You can choose forums
which will not have topics, but will be just containers for other
forums. This lets you both group and nest forums.'), 1, '<'. t('none')
.'>');
$output = form_group(t('Forum structure settings'), $group);
$group = form_textarea(t('Explanation or submission
guidelines'), 'forum_help', variable_get('forum_help', ''), 70, 5,
t('This text will be displayed at the top of the forum submission form.
It is useful for helping or instructing your users.'));
$group .= form_textfield(t('Forum icon path'), 'forum_icon_path',
variable_get('forum_icon_path', ''), 30, 255, t('The path to the forum
icons. Leave blank to disable icons. Don\'t add a trailing slash.
Default icons are available in the "misc" directory.'));
$group .= form_select(t('Hot topic threshold'),
'forum_hot_topic', variable_get('forum_hot_topic', 15),
drupal_map_assoc(array(5, 10, 15, 20, 25, 30, 35, 40, 50, 60, 80, 100,
1)), t('The number of posts a topic must have to be considered
hot.'));
$group .= form_select(t('Topics per page'), 'forum_per_page',
variable_get('forum_per_page', 25), drupal_map_assoc(array(10, 25, 50,
75, 100)), t('The default number of topics displayed per page; links to
browse older messages a
Re: [GENERAL] warning: pg_query(): Query failed
hi bruno, turns out that l.last_comment_uid and l.last_comment_name are integer and char var respectively. since i'm using 7.4.1 with strict boolean casting, is there a better alternative to instantiating a different version of pgsql on my server? In the php code you showed the following fragment: IF(l.last_comment_uid, cu.name, l.last_comment_name) I doubt that l.last_comment_uid is a boolean based on its name. If it isn't this is probably the source of your problem. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] ---(end of broadcast)--- TIP 8: explain analyze is your friend
[GENERAL] warning: pg_query(): Query failed
hi bruno,
yeah, the postgresql problem was fixed by changing IF statements to
CASE statements in forum.module code:
// remove:
-- $topic = db_fetch_object(db_query_range('SELECT DISTINCT(n.nid),
l.last_comment_timestamp, IF(l.last_comment_uid, cu.name,
l.last_comment_name) as last_comment_name, l.last_comment_uid FROM
{node} n ' . node_access_join_sql() . ", {node_comment_statistics} l
/*! USE INDEX (node_comment_timestamp) */, {users} cu, {term_node} r
WHERE n.nid = r.nid AND r.tid = %d AND n.status = 1 AND n.type =
'forum' AND l.last_comment_uid = cu.uid AND n.nid = l.nid AND " .
node_access_where_sql() . ' ORDER BY l.last_comment_timestamp DESC',
$forum->tid, 0, 1));
// add:
++ $topic = db_fetch_object(db_query_range('SELECT DISTINCT(n.nid),
l.last_comment_timestamp, CASE WHEN l.last_comment_uid = 1 THEN cu.name
ELSE l.last_comment_name END as last_comment_name, l.last_comment_uid
FROM {node} n ' . node_access_join_sql() . ", {node_comment_statistics}
l, {users} cu, {term_node} r WHERE n.nid = r.nid AND r.tid = %d AND
n.status = 1 AND n.type = 'forum' AND l.last_comment_uid = cu.uid AND
n.nid = l.nid AND " . node_access_where_sql() . ' ORDER BY
l.last_comment_timestamp DESC', $forum->tid, 0, 1));
thanks for all the support!
aaron
On Jan 6, 2005, at 10:25 PM, Bruno Wolff III wrote:
On Thu, Jan 06, 2005 at 17:32:30 -0800,
Aaron Steele <[EMAIL PROTECTED]> wrote:
hi bruno,
turns out that l.last_comment_uid and l.last_comment_name are integer
and char var respectively. since i'm using 7.4.1 with strict boolean
casting, is there a better alternative to instantiating a different
version of pgsql on my server?
A better solution is fixing your code. What do you expect it to do
anyhow? Once you figure out what you want it to do, you should be able
to write a boolean expression that is true, false or null when you
want it to be. If fact from what I saw it seems that you could just use
a CASE expression and skip the function call altogether.
---(end of
broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
