Fwd: Unexpected Multiple Records from Randomized Query
Hello. I encountered a problem while setting up a test environment to learn the use of the random() function. The query is as follows: CREATE TABLE users ( id SERIAL PRIMARY KEY, name VARCHAR(100), email VARCHAR(100), signup_date DATE ); INSERT INTO users (name, email, signup_date) SELECT 'User ' || i, 'user' || i || '@example.com', NOW() - (random() * (365 * 5) || ' days')::interval FROM generate_series(1, 100) AS s(i); explain analyze SELECT * FROM users WHERE name = 'User '||trunc(random()*100) ; I expected the result to return one record. However, in some cases, the result comes back with 2 or 3 records. What am I doing wrong? I'm not sure if this is a conflict issue between the random() and trunc() functions, or if I have set up the test case incorrectly. I am using PostgreSQL 15.4 version on Ubuntu 22.04 in a container environment. PostgreSQL was built by compiling the Source code. Thank you for your time and consideration. Best regards, Myoungjun Kim
Re: Creating table and indexes for new application
On Fri, 23 Feb, 2024, 1:28 pm yudhi s, wrote: > > > On Fri, 23 Feb, 2024, 1:20 pm sud, wrote: > >> >> >> On Fri, 23 Feb, 2024, 12:41 pm Laurenz Albe, >> wrote: >> >>> On Fri, 2024-02-23 at 02:05 +0530, yudhi s >>> >>> > 2)Should we be creating composite indexes on each foreign key for >>> table2 and table3, because >>> > any update or delete on parent is going to take lock on all child >>> tables? >>> >>> Every foreign key needs its own index. A composite index is only >>> appropriate if the foreign >>> key spans multiple columns. >>> >>> >> >> From the DDL which OP posted it's using composite foreign key thus a >> composite index would be needed. >> However, if someone doesn't delete or update the parent table PK , is >> it still advisable to have all the FK indexed? Like in general I think >> transaction id should not get updated in a normal scenario unless some >> special case. >> >> >> > Thank you. I can double check if we have confirmed use case of deleting > the parent table or updating PK in the parent table. But anyway it can > happen for data fix for sure in some scenario. > > But yes, we are certainly going to drop/purge partition from all the > parent and child table after specific days. So isn't that need the FK to be > indexed or else it will scan whole parent table partition? > I am not sure if drop partition of parent table, will have a lock or will do a full scan on the child table while doing the partition maintenance or dropping the partitions, in absence of foreign key index. Others may comment here. > >>
Unable to get PostgreSQL 15 with Kerberos (GSS) working
Hi All
Would really like some help to get this working, I'm not sure where to turn
next?
About things:
PostgreSQL 15
Running of RHEL8
Using official repo
Build-in OS version(s) of PostgreSQL are disabled
Postgresql15-server & postgresql15-contrib installed via dnf
PostgreSQL 15 up to date via dnf update
RHEL8
Active directory domain joined, Samba server with SSSD with Winbind
(https://access.redhat.com/solutions/3802321)
Active Directory
Running in 2016 compatibility mode
Generally
Apart from not being able to get Kerberos working for PostgreSQL the system is
stable and working as expected
Issue:
No matter what I try I don't seem to be able to get the psl command locally to
work using Kerberos. I receive for following message:
FATAL: GSSAPI authentication failed for user "postgres"
FATAL: GSSAPI authentication failed for user myad.usern...@mydomain.net
Using the kinit command I can get a Kerberos ticket both via the created keytab
file for the postgres user or for my AD account. I've even intentional let the
ticket run out and received the warning on screen that my 'Ticket expired'.
My Current Config:
/etc/krb5.conf
# To opt out of the system crypto-policies configuration of krb5, remove the
# symlink at /etc/krb5.conf.d/crypto-policies which will not be recreated.
includedir /etc/krb5.conf.d/
[logging]
default = FILE:/var/log/krb5libs.log
kdc = FILE:/var/log/krb5kdc.log
admin_server = FILE:/var/log/kadmind.log
[libdefaults]
default_realm = MYDOMAIN.NET
#dns_lookup_realm = true
dns_lookup_kdc = true
forwardable = true
udp_preference_limit = 1
# Values for next three parameters should be used from Default Domain Policy GPO
# Default Domain Policy \ Computer Configuration \ Policies \ Windows Settings
\ ...
# ... \ Security Settings Account Policies \ Kerberos Policy
# Maximum lifetime for user ticket
ticket_lifetime = 10h
# Maximum lifetime for user ticket renewal
renew_lifetime = 7d
# Maximum tolerance for computer clock synchronization
clockskew = 300
[realms]
MYDOMAIN.NET = {
admin_server = uk-ref1-dc2.mydomain.net
kdc = uk-ref1-dc2.mydomain.net
kdc = uk-ref2-dc1.mydomain.net
kdc = uk-ref3-dc1.mydomain.net
kdc = uk-ref3-dc2.mydomain.net
}
[domain_realm]
.mydomain.net = MYDOMAIN.NET
mydomain.net = MYDOMAIN.NET
Active Directory User for the Service Principal Name (SPN)
User created 'pg_hostname' (15 characters in length)
User properties changed
Delegation tab: Enable - Trust this user for delegation to any service
(Kerberos Only)
Account tab: Enable - This account supports Kerberos AES 128 bit encryption
Account tab: Enable - This account supports Kerberos AES 256 bit encryption
Password reset at this point
Generate the postgres.keytab File
ktpass /out C:\1\postgres.keytab /princ
POSTGRES/hostname.mydomain@mydomain.net /mapuser pg_hostname /crypto
AES256-SHA1 +rndpass /target MYDOMAIN.NET -ptype KRB5_NT_PRINCIPAL
...Confirmed using 'setspn -L pg_hostname'
Setup the postgres.keytab File on the PostgreSQL Server
Copied to /pgcluster/data
Owner/group set to postgres
Permissions set to 0400 (read)
Update the postgresql.conf to reference the postgres.keytab file
krb_server_keyfile = '/pgcluster/data/postgres.keytab'
Update the pg_hba.conf to Enable Kerberos Authentication
hostall all ::1/128 gss
... only this while testing directly on the server
... place at top of IPv6 section
Created a Domain Test account via psql command
create user " myad.usern...@mydomain.net"; CREATE ROLE
Restart PostgreSQL
systemctl restart postgresql-15
Testing
kinit -kt /pgcluster/data/postgres.keytab
POSTGRES/hostname.mydomain@mydomain.net
klist
Ticket cache: KCM:0:20151
Default principal: POSTGRES/hostname.mydomain@mydomain.net
Valid starting ExpiresService principal
23/02/24 10:19:12 23/02/24 20:19:12 krbtgt/mydomain@mydomain.net
renew until 23/02/24 20:19:12
psql -h localhost -U postgres -d postgres
... logs show: GSSAPI authentication failed for user "postgres"
kinit myad.usern...@mydomain.net
klist
Ticket cache: KCM:0:54648
Default principal: myad.usern...@mydomain.net
Valid starting ExpiresService principal
23/02/24 11:56:51 23/02/24 21:56:48 krbtgt/MYDOMAIN.NET@ MYDOMAIN.NET
renew until 23/02/24 21:56:51
psql -h localhost -U myad.username -d postgres
... logs show: GSSAPI authentication failed for user " myad.username "
Tried increasing logging but did not get any additional info related to the
failure
Regards
Matt Dennison
Re: How to schema-qualify "IS DISTINCT FROM" on trigger definition? (I created a db that cannot be dump/restored)
On Thu, Feb 22, 2024 at 5:06 PM Erik Wienhold wrote: > On 2024-02-22 22:14 +0100, Vick Khera wrote: > > On Wed, Feb 21, 2024 at 4:27 PM Tom Lane wrote: > > > > > For the moment, I think the only feasible solution is for your trigger > > > function to set the search path it needs by adding a "SET search_path > > > = whatever" clause to the function's CREATE command. > > > > > > The error is not in the function, it is the WHEN clause of the trigger. > > There's no way to set a search path on the trigger as far as I see. > > > > The only option I see is to remove the WHEN clause on the trigger and > wrap > > my function with an IF with those same conditions. I hope this will not > > result in any noticeable difference in speed. > > You may also try the equivalent CASE expression in the WHEN clause. > > > https://wiki.postgresql.org/wiki/Is_distinct_from#Writing_with_CASE_statements > Nice. It makes for a big ugly trigger statement, but probably my better choice. I was considering doing this but didn't want to risk making my own interpretation.
Re: Fwd: Unexpected Multiple Records from Randomized Query
=?UTF-8?B?6rmA66qF7KSA?= writes: > explain analyze > SELECT * FROM users WHERE name = 'User '||trunc(random()*100) ; > I expected the result to return one record. However, in some cases, the > result comes back with 2 or 3 records. What am I doing wrong? random() is re-evaluated at each row, so it's not that surprising if you sometimes get multiple matches. This is the same behavior that you relied on to fill the table with not-all-the-same names. The preferred way to avoid that is to stuff the random() call into a CTE: WITH x AS (SELECT random() AS r) SELECT * FROM users, x WHERE name = 'User '||trunc(r*100) ; or in this case better to shove the whole constant computation into the CTE. regards, tom lane
