What is the difference in writing test cases and writing Sanity test cases for sign up of any website? Could anyone please let me understand by giving few test cases or example of Sanity test cases for sign up page of any website?
Best posts made by nishika
Latest posts made by nishika
RE: PostgreSQL triggered update function lock mechanism
The magic is called "row lock". The updates on the counter column are serialized by row locks, so there cannot be any race condition. A lost update can never happen if you read and write the data in a single
Is wal_keep_segments required with streaming replication when archive and restore commands are set?
I have a 2 node postresql(12.9) cluster with streaming replication enabled. I have also set the archive and restore commands which I verified is working as expected. No replication slots or wal_keep_segments configured.
archive_command ='test ! -f /archive_location/%f && cp %p /archive_location/%f' restore_command = 'cp /archive_location/%f %p'
In this setup, I notice that if the standby disconnects after receiving a partial WAL file and if the same WAL gets archived and recycled from pg_wal on the primary node, then on reconnection, this standby never catches up with primary.
The error seen on standby is -
ERROR: requested WAL segment 0000000100000000000000XX has already been removed.
Debugging revealed the following -
1- Issue only seen when partial WAL file written on standby at time of disconnection. Since streaming replication is enabled, postgres doesn't wait for the 16MB WAL segment to fill up before shipping WAL to standby. So there could be partially written WAL on standby at any point. Confirmed by file compare of same WAL on primary and standby -
cmp 0000000100000000000000XX /tmp/0000000100000000000000XX 0000000100000000000000XX /tmp/0000000100000000000000XX differ: byte 15722105, line 68077
On reconnection, postgres first tries restoring WAL from archive but on failure, falls on streaming the same WAL from publisher over TCP connection. Since wal_keep_segments is not set, this WAL is recycled from pg_wal on publisher and standby is now stuck in this loop
628ed196.bd8bf 0DEBUG: 00000: switched WAL source from stream to archive after failure 2 628ed196.bd8bf 0DEBUG: 00000: record with incorrect prev-link 1/FDFF2008 at 0/EDEFE678 2022-05-26 06:32:19 IST 776383 628ed196.bd8bf 0LOCATION: ReadRecord, xlog.c:4348 2022-05-26 06:32:19 IST 776383 628ed196.bd8bf 0DEBUG: 00000: switched WAL source from archive to stream after failure 2022-05-26 06:32:19 IST 776629 628ed19b.bd9b5 0FATAL: XX000: could not receive data from WAL stream: ERROR: requested WAL segment 0000000100000000000000XX has already been removed
Given this info looks like wal_keep_segments is needed even when archive + restore is set for streaming replication. Is this true? Am I missing something?
Edit - Adding necessary log info here for improved readability -
628ed196.bd8bf 0DEBUG: 00000: switched WAL source from stream to archive after failure 2022-05-26 06:32:19 IST 776383 628ed196.bd8bf 0LOCATION: WaitForWALToBecomeAvailable, xlog.c:12208 2022-05-26 06:32:19 IST 776383 628ed196.bd8bf 0DEBUG: 00000: record with incorrect prev-link 1/FDFF2008 at 0/EDEFE678 2022-05-26 06:32:19 IST 776383 628ed196.bd8bf 0LOCATION: ReadRecord, xlog.c:4348 2022-05-26 06:32:19 IST 776383 628ed196.bd8bf 0DEBUG: 00000: switched WAL source from archive to stream after failure 2022-05-26 06:32:19 IST 776381 628ed196.bd8bd 0LOCATION: LogChildExit, postmaster.c:3697 2022-05-26 06:32:19 IST 776629 628ed19b.bd9b5 0LOG: 00000: started streaming WAL from primary at 0/ED000000 on timeline 1 2022-05-26 06:32:19 IST 776629 628ed19b.bd9b5 0LOCATION: WalReceiverMain, walreceiver.c:372 2022-05-26 06:32:19 IST 776629 628ed19b.bd9b5 0FATAL: XX000: could not receive data from WAL stream: ERROR: requested WAL segment 0000000100000000000000ED has already been removed
RE: Fuzzy searching through multiple fields in postgreSQL
You could use the word similarity operator
SELECT ... FROM tab WHERE 'postgres'
To speed that up, you can create a GIN index on that expression:
CREATE INDEX ON tab USING gin (concat(title, ' ', description) gin_trgm_ops);
You can adjust the parameter
pg_trgm.word_similarity_thresholdto get the desired sensitivity.
Software to help automate windows os patching with SQL Server high availability/failover clusters
is there a third party tool or a way to automate installing windows updates on windows clusters with sql server high availability groups installed? right now we have a manual process of installing then failing over etc.
Does SQL server provide automatic page repair in the async commit mode AG?
I understand that SQL AG supports the automatic page repair when the primary is synchronized with the secondary.
Suppose the AG is configured in an async commit mode, then does also mean that the primary and secondary are synchronized (I know there would be a delay), there by providing for automatic page repair?
RE: How does SQL server handle the password change of the service account?
When a new password is to be used, you need to restart (=stop and start) the service. See the https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/scm-services-change-the-service-startup-account?view=sql-server-ver15 :
When you change the service startup account for the Database Engine and SQL Server Agent, the SQL Server service (the Database Engine) must be restarted for the change to take effect. When the service is restarted, all databases associated with that instance of SQL Server will be unavailable until the service successfully restarts.
Now, why does changing the password require a restart? That's because when a process starts, it generates a https://docs.microsoft.com/en-us/windows/win32/secauthz/access-tokens . The token's validity depends on its password. If you change the account password on AD and for the service, the current process still keeps using the old password. In a while, that leads to locking the account, as it tires to re-authenticate with the old (=invalid) password.
Oh, and don't change the service account's password https://pages.nist.gov/800-63-FAQ/#q-b06 it's too old. If frequent changes are needed, use virtual or managed accounts instead.
PostgreSQL function call error: structure of query does not match function result type
i have function
CREATE FUNCTION create_post( txt text, created BIGINT, photourl text, user_id bigint ) RETURNS TABLE( id bigint, "text" text, created_at timestamp, photo_url text, user_email text, comments_count int ) AS $$ DECLARE ret_id bigint;
INSERT INTO posts(
VALUES ($1, to_timestamp($2), $3, $4)
RETURNING posts.id INTO ret_id;
SELECT p.id AS id,
p.text AS "text",
p.created_at AS created_at,
p.photo_url AS photo_url,
u.email AS user_email,
COUNT(c) AS comments_count
FROM posts AS p
INNER JOIN users AS u ON u.id = p.user_id
LEFT JOIN comments AS c ON c.post_id = p.id
WHERE p.id = ret_id
GROUP BY p.id,
END $$ LANGUAGE plpgsql;
It created successfully, but when call function
SELECT create_post('haha', 1652131509, 'photo', 2);get error
structure of query does not match function result type. Used tables:
CREATE TABLE "users" ( "id" BIGSERIAL PRIMARY KEY, "email" text UNIQUE NOT NULL, "encrypted_password" text NOT NULL, "created_at" timestamp NOT NULL );
CREATE TABLE "posts" (
"id" BIGSERIAL PRIMARY KEY,
"created_at" timestamp NOT NULL,
"user_id" bigint NOT NULL,
FOREIGN KEY ("user_id") REFERENCES "users" ("id") ON DELETE CASCADE
Postgres equivalent of Oracle's RMAN.VALIDATE_DATABASE
I am migrating a system using Oracle DB to Postgres (12.7). Various checks are run weekly against the existing DB, including a RMAN.VALIDATE_DATABASE (actually RDSADMIN.RDSADMIN_RMAN_UTIL.VALIDATE_DATABASE as it's Amazon RDS) - physical+logical type. Is there an equivalent to this with Postgres?
The Oracle DB runs as an Amazon RDS instance, the Postgres DB is running as an Amazon Aurora instance.
I can see that I can enable block checksums, but I don't see anything else.